求职员经过的部门SQL

[hc8112_boy]

Create Table #t(iid int identity(1,1),Personcode nvarchar(20),depCode nvarchar(10))
go

Insert into #t(Personcode,depCode) values ('1001','11')
Insert into #t(Personcode,depCode) values ('1001','11')
Insert into #t(Personcode,depCode) values ('1001','21')
Insert into #t(Personcode,depCode) values ('1001','21')
Insert into #t(Personcode,depCode) values ('1001','23')
Insert into #t(Personcode,depCode) values ('1001','23')
Insert into #t(Personcode,depCode) values ('1001','11')
Insert into #t(Personcode,depCode) values ('1001','25')
Insert into #t(Personcode,depCode) values ('1001','15')
Insert into #t(Personcode,depCode) values ('1001','21')
Insert into #t(Personcode,depCode) values ('1001','21')
go
Insert into #t(Personcode,depCode) values ('1002','05')
Insert into #t(Personcode,depCode) values ('1002','10')
Insert into #t(Personcode,depCode) values ('1002','10')
Insert into #t(Personcode,depCode) values ('1002','15')
Insert into #t(Personcode,depCode) values ('1002','15')
Insert into #t(Personcode,depCode) values ('1002','05')
Insert into #t(Personcode,depCode) values ('1002','15')
Insert into #t(Personcode,depCode) values ('1002','20')
Insert into #t(Personcode,depCode) values ('1002','20')

需要得到的结果,personcode经过的depcode,按id排序后去掉重复行

1001 11-21,23-11-25-15-21
1002 05-10-15-05-15-20

[stelf]

select personcode, depcode = (stuff((select distinct ',' + depcode from #t as b where b.personcode = a.personcode for xml path('')), 1, 1, '')) from #t a group by a.personcode;

[二月十六]

;WITH ctea AS (
SELECT 
       iid,
       ROW_NUMBER() OVER (PARTITION BY Personcode, depCode ORDER BY iid) num,
       ROW_NUMBER() OVER (PARTITION BY Personcode, depCode ORDER BY iid) - iid rn,
       Personcode,
       depCode
  FROM #t
),cteb AS (
SELECT *,ROW_NUMBER()OVER(PARTITION BY rn,ctea.Personcode,ctea.depCode ORDER BY ctea.iid) rn1 FROM ctea
),ctec AS (
SELECT * FROM cteb WHERE rn1=1 
)
select personcode,
       depcode = (stuff((select  '-' + depcode
                          from ctec as b
                         where b.personcode = a.personcode ORDER BY iid
                           for xml path('') ),
                        1,
                        1,
                        ''))
  from ctec a
 group by a.personcode;

[听雨停了]

;WITH cte AS (
	SELECT *,iid-ROW_NUMBER() OVER(PARTITION BY personCode,depcode ORDER BY iid) AS col 
	FROM #t
),cte2 AS (
	SELECT *,ROW_NUMBER() OVER(PARTITION BY personCode,depCode,col ORDER BY iid) AS rn 
	FROM cte
),cte3 AS (
	SELECT iid,personCode,depCode 
	FROM cte2
	WHERE rn=1
)
SELECT B.personCode,
       LEFT(DepCodeList, LEN(DepCodeList) -1) AS DepCodeList
FROM   (
           SELECT personCode,
                  (
                      SELECT depCode + '-'
                      FROM   cte3
                      WHERE  personCode = A.personCode
                      ORDER BY
                             iid
                             FOR XML PATH('')
                  ) AS DepCodeList
           FROM   cte3 A
           GROUP BY
                  personCode
       ) B;

personCode           DepCodeList
-------------------- ----------------------
1001                 11-21-23-11-25-15-21
1002                 05-10-15-05-15-20

[leo_lesley]

--去除连续的重复信息
;WITH cet AS(
SELECT Personcode,depcode,MAX(iid) iid
FROM (
SELECT id = iid - ROW_NUMBER() OVER(PARTITION BY Personcode,depCode ORDER BY iid),* 
FROM #t ) t
GROUP BY id,Personcode,depCode

) 
--输出结果
SELECT Personcode,depcode = STUFF((SELECT '-' + depcode FROM cet WHERE Personcode = t.Personcode ORDER BY iid FOR XML PATH('') ),1,1,'')
FROM cet t
GROUP BY Personcode

[leo_lesley]

Create Table #t(iid int identity(1,1),Personcode nvarchar(20),depCode nvarchar(10))
go
 
Insert into #t(Personcode,depCode) values ('1001','11')
Insert into #t(Personcode,depCode) values ('1001','11')
Insert into #t(Personcode,depCode) values ('1001','21')
Insert into #t(Personcode,depCode) values ('1001','21')
Insert into #t(Personcode,depCode) values ('1001','23')
Insert into #t(Personcode,depCode) values ('1001','23')
Insert into #t(Personcode,depCode) values ('1001','11')
Insert into #t(Personcode,depCode) values ('1001','25')
Insert into #t(Personcode,depCode) values ('1001','15')
Insert into #t(Personcode,depCode) values ('1001','21')
Insert into #t(Personcode,depCode) values ('1001','21')
go
Insert into #t(Personcode,depCode) values ('1002','05')
Insert into #t(Personcode,depCode) values ('1002','10')
Insert into #t(Personcode,depCode) values ('1002','10')
Insert into #t(Personcode,depCode) values ('1002','15')
Insert into #t(Personcode,depCode) values ('1002','15')
Insert into #t(Personcode,depCode) values ('1002','05')
Insert into #t(Personcode,depCode) values ('1002','15')
Insert into #t(Personcode,depCode) values ('1002','20')
Insert into #t(Personcode,depCode) values ('1002','20')

--去除连续的重复信息
;WITH cet AS(
SELECT Personcode,depcode,MAX(iid) iid
FROM (
SELECT id = iid - ROW_NUMBER() OVER(PARTITION BY Personcode,depCode ORDER BY iid),* 
FROM #t ) t
GROUP BY id,Personcode,depCode

) 
--输出结果
SELECT Personcode,depcode = STUFF((SELECT '-' + depcode FROM cet WHERE Personcode = t.Personcode ORDER BY iid FOR XML PATH('') ),1,1,'')
FROM cet t
GROUP BY Personcode


DROP TABLE #t

/*
需要得到的结果,personcode经过的depcode,按id排序后去掉重复行 

1001  11-21,23-11-25-15-21
1002  05-10-15-05-15-20


*/

[hc8112_boy]

天哪，现在CSDN改的怎么给大家分啊，一点结帖直接就跑了。。。