重复行仅显示第一行
大约 2 分钟
示例代码范本
case row_number() over(partition by {唯一字段值} order by {当前字段值}
when 1 then {当前字段值}
else null end as {字段名称}
注意
{唯一字段值} 可以等于{当前字段值} 如果报表行内有类似于单据编号、物料编号等可参考的唯一值,可取这类字段作为
- 关键的函数:
row_number()
over()
实现逻辑
通过row_number()配合over生成以T0.[OpprID]为分组的序号,order by因为是必须,所以无所谓。然后现在在同一个T0.[OpprID]分组下的多个cardcode,就会生成1、2、3……这样的序号,那么case判断生产的序号是不是1,就是第一个,是的话就输出cardcode,字段名,不是,那说明就不再输出,所以输出null
首先,row_number()是输出1、2、3、4……这样的顺序号的 其次,over开窗函数就限定了row_number()生成这样序列的范围(partition by限定的)
第三,基于上两个条件,先不说case when的情况下,应该输出的内容大概是 单据号 名称 ,row_number()生成内容
1001 xxx 1
1001 xxx 2
1002 yyy 1
1002 yyy 2
根据要求,只显示第一行的xxx或者yyy,那么是不是case when row_number()生成值为1的,就保留名称值,否则就是null
示例代码
SELECT T0.[OpprId]
, CASE ROW_NUMBER() OVER (PARTITION BY T0.[OpprID] ORDER BY T0.cardcode)
WHEN 1 THEN T0.cardcode
ELSE NULL
END AS 客户编码
, CASE ROW_NUMBER() OVER (PARTITION BY T0.[OpprID] ORDER BY T0.[Cardname])
WHEN 1 THEN T0.[Cardname]
ELSE NULL
END AS 客户名称
, CASE ROW_NUMBER() OVER (PARTITION BY T0.[OpprID] ORDER BY T1.[CntctPrsn])
WHEN 1 THEN T1.[CntctPrsn]
ELSE NULL
END AS 联系人
, CASE ROW_NUMBER() OVER (PARTITION BY T0.[OpprID] ORDER BY T2.[GroupName])
WHEN 1 THEN T2.[GroupName]
ELSE NULL
END AS 等级
, CASE ROW_NUMBER() OVER (PARTITION BY T0.[OpprID] ORDER BY T1.[IntrntSite])
WHEN 1 THEN T1.[IntrntSite]
ELSE NULL
END AS 网站, T0.[PrjCode], T5.[Descript] AS '机会阶段', T0.[MaxSumLoc] AS '潜在金额'
, T0.[Memo] AS '客户真实需求', T0.[Status]
FROM OOPR T0
INNER JOIN OCRD T1 ON T0.[CardCode] = T1.[CardCode]
INNER JOIN OCRG T2 ON T1.[GroupCode] = T2.[GroupCode]
INNER JOIN OPR4 T3 ON T0.[OpprId] = T3.[OprId]
INNER JOIN OOST T5 ON T0.[StepLast] = T5.[Num]
INNER JOIN OPR3 T6 ON T6.opportid = t0.opprid