跳至主要內容

重复行仅显示第一行

五六零网校大约 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