5,889
社区成员
发帖
与我相关
我的任务
分享
WITH MEDIUM(DT,NM,GPNM) AS (
SELECT DISTINCT DEPART,CAST('' AS VARCHAR(100)),0 FROM MYTABLE
UNION ALL
SELECT DEPART,NAME,ROWNUMBER() OVER(PARTITION BY DEPART) FROM MYTABLE
),
TEMP(DPT,NAM,GRPNUM) AS (
SELECT * FROM MEDIUM WHERE NM=''
UNION ALL
SELECT DT,CAST(NM||','||NAM AS VARCHAR(100)),GRPNUM+1 FROM TEMP,MEDIUM WHERE DPT=DT AND GRPNUM=GPNM-1
)
SELECT DPT,LEFT(NAM,LENGTH(NAM)-1) FROM TEMP A WHERE A.GRPNUM=(SELECT MAX(GRPNUM) FROM TEMP B WHERE B.DPT=A.DPT)