应用场景:
有一个员工结薪周期内的排班表,列分别为([Guid],[workId],[dutyDate])表结构如下:
workId 工号 dutyDate 排班日期 ,如下下图红圈内为一周排班
这时,要求查询排班表,按每行显示每个员工在一个结薪周期内的排班结果集
举例三个工号,代码如下:
use [GSCenter01DB]
go
--临时表,取出三个人工号在结薪周期内的所以排班日期
select [Guid],[workId],[dutyDate] into #T1 from duty_empSchedule
where dutyDate between '2018/12/26' and '2019/01/25' and enabled='Y' and workId in('K17000205','K17001422','K17000658','k16002598')
--select * from #T1
select workId,(select dutyDate+',' FROM #T1 where workId =A.workid FOR XML PATH('')) as List from #T1 A group by workid
drop table #T1
运行效果,如下:
分析:
select workId,(select dutyDate+',' FROM #T1 where workId =A.workid FOR XML PATH('')) as List from #T1 A
通过FOR XML PATH 将某一工号如K17000205的排班日期,显示成格式为:“ 日期1,日期2,日期2,”的格式!
group by workid ---将表分组
可以看到List列里面的数据都会多出一个逗号
SELECT B.workid,LEFT(List,LEN(List)-1) as EmpAllDutyDate from (...) B 此处...带入上述代码,如下
再次整理,完整的代码如下一份
use [GSCenter01DB]
go
select [Guid],[workId],[dutyDate] into #T1 from duty_empSchedule
where dutyDate between '2018/12/26' and '2019/01/25' and enabled='Y' and workId in('K17000205','K17001422','K17000658','k16002598')
--select * from #T1
--select workId,(select dutyDate+',' FROM #T1 where workId =A.workid FOR XML PATH('')) as List from #T1 A group by workid
SELECT B.workid,LEFT(List,LEN(List)-1) as EmpAllDutyDate from
(
select workId,(select dutyDate+',' FROM #T1 where workId =A.workid FOR XML PATH('')) as List from #T1 A group by workid
)B
drop table #T1
运行效果,大家自行观测