34,594
社区成员
发帖
与我相关
我的任务
分享
;WITH tb(dept,sh_name,sh_sex,sh_loc,sh_in_d,sh_age,sh_d,ks_name,ks_sex,ks_loc,ks_in_d,ks_age,ks_d)AS
(
SELECT 'abc',NULL,NULL,NULL,NULL,NULL,NULL,'a','f','hb','2016-12-14',39,'n' UNION
SELECT 'abc',NULL,NULL,NULL,NULL,NULL,NULL,'b','m','hb','2016-12-15',39,'n' UNION
SELECT 'abc',NULL,NULL,NULL,NULL,NULL,NULL,'c','f','hb','2016-12-16',39,'n' UNION
SELECT 'abc',NULL,NULL,NULL,NULL,NULL,NULL,'d','m','hb','2016-12-17',39,'n' UNION
SELECT 'abc',NULL,NULL,NULL,NULL,NULL,NULL,'e','f','hb','2016-12-18',39,'n' UNION
SELECT 'abc',NULL,NULL,NULL,NULL,NULL,NULL,'f','m','hb','2016-12-19',39,'n' UNION
SELECT 'abc',NULL,NULL,NULL,NULL,NULL,NULL,'g','f','hb','2016-12-20',39,'n' UNION
SELECT 'abc','aa','f','ah','2016-07-26',47,'n',NULL,NULL,NULL,NULL,NULL,NULL UNION
SELECT 'abc','bb','f','ah','2016-07-25',47,'n',NULL,NULL,NULL,NULL,NULL,NULL UNION
SELECT 'abc','cc','f','ah','2016-07-24',47,'n',NULL,NULL,NULL,NULL,NULL,NULL UNION
SELECT 'abc','dd','f','ah','2016-07-23',47,'n',NULL,NULL,NULL,NULL,NULL,NULL
)
,m AS(
SELECT *,ROW_NUMBER()OVER(PARTITION BY tb.dept ORDER BY sh_name,sh_sex,sh_loc,sh_in_d,sh_age,sh_d) AS sh_rn
,ROW_NUMBER()OVER(PARTITION BY tb.dept ORDER BY ks_name,ks_sex,ks_loc,ks_in_d,ks_age,ks_d) AS ks_rn
FROM tb
)
SELECT sh.dept,sh.sh_name,sh.sh_sex,sh.sh_loc,sh.sh_in_d,sh.sh_age,sh.sh_d,ks.ks_name,ks.ks_sex,ks.ks_loc,ks.ks_in_d,ks.ks_age,ks.ks_d
FROM m AS sh
INNER JOIN m AS ks ON sh.dept=ks.dept AND sh.sh_rn=ks.ks_rn
ORDER BY sh.dept, sh.sh_rn desc
dept sh_name sh_sex sh_loc sh_in_d sh_age sh_d ks_name ks_sex ks_loc ks_in_d ks_age ks_d
abc dd f ah 2016-07-23 47 n g f hb 2016-12-20 39 n
abc cc f ah 2016-07-24 47 n f m hb 2016-12-19 39 n
abc bb f ah 2016-07-25 47 n e f hb 2016-12-18 39 n
abc aa f ah 2016-07-26 47 n d m hb 2016-12-17 39 n
abc NULL NULL NULL NULL NULL NULL c f hb 2016-12-16 39 n
abc NULL NULL NULL NULL NULL NULL b m hb 2016-12-15 39 n
abc NULL NULL NULL NULL NULL NULL a f hb 2016-12-14 39 n
abc NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
abc NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
abc NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
abc NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL