现在我有如下sql语句,表名为:Attendance
select a.Attendance_Users_ID,SUBSTRING(a.Attendance_Accountday,7,8) as 日期,a.Attendance_UserName,
SUBSTRING(CONVERT(varchar, a.Attendance_StartWork,114),0,6) as startWork,SUBSTRING(CONVERT(varchar, a.Attendance_StopWork,114),0,6) as StopWork from Attendance a
其中的"日期"我是截取后面两位,来作为每个月的多少号,其中所有的字段都在一个表中得到的结果为:
Attendance_Users_ID 日期 Attendance_UserName startWork StopWork
A001 17 admin 8:30 21:45
A001 16 admin 5:30 12:23
A001 15 admin 12:30 21:00
A002 14 wilm 19:30 23:45
A002 13 wilm 07:30 13:25但我想要的结果为:
Attendance_Users_ID Attendance_UserName 日期1 startWork StopWork 日期2 startWork StopWork 日期3 startWork StopWork A001 admin 17 8:30 21:45 16 5:30 12:23 12:30 21:00 A002 wilm 14 19:30 23:45 13 07:30 13:25
其实我是要做一个员工的交班的报表,要显示的是,每个员工在一个月的上下班时间交班情况,或许我的思路是有问题的,有没有更好的解决办法,请高手帮帮忙,谢谢!
select a.Attendance_Users_ID,SUBSTRING(a.Attendance_Accountday,7,8) as 日期,a.Attendance_UserName,
SUBSTRING(CONVERT(varchar, a.Attendance_StartWork,114),0,6) as startWork,SUBSTRING(CONVERT(varchar, a.Attendance_StopWork,114),0,6) as StopWork from Attendance a
其中的"日期"我是截取后面两位,来作为每个月的多少号,其中所有的字段都在一个表中得到的结果为:
Attendance_Users_ID 日期 Attendance_UserName startWork StopWork
A001 17 admin 8:30 21:45
A001 16 admin 5:30 12:23
A001 15 admin 12:30 21:00
A002 14 wilm 19:30 23:45
A002 13 wilm 07:30 13:25但我想要的结果为:
Attendance_Users_ID Attendance_UserName 日期1 startWork StopWork 日期2 startWork StopWork 日期3 startWork StopWork A001 admin 17 8:30 21:45 16 5:30 12:23 12:30 21:00 A002 wilm 14 19:30 23:45 13 07:30 13:25
其实我是要做一个员工的交班的报表,要显示的是,每个员工在一个月的上下班时间交班情况,或许我的思路是有问题的,有没有更好的解决办法,请高手帮帮忙,谢谢!
;with tab(Attendance_Users_ID,日期, Attendance_UserName, startWork, StopWork)
as
(
select 'A001',17, 'admin', '8:30', '21:45'
union select 'A001', 16, 'admin', '5:30', '12:23'
union select 'A001', 15, 'admin', '12:30', '21:00'
union select 'A002', 14, 'wilm', '19:30', '23:45'
union select 'A002', 13, 'wilm', '07:30', '13:25'
)
--select Attendance_Users_ID,Attendance_UserName,cast(日期 as varchar)+' ,'+startWork+' ,'+StopWork from tabselect Attendance_Users_ID,Attendance_UserName,
scort=STUFF((SELECT ';【日期'+cast(日期 as varchar)+' ,开始时间'+startWork+' ,结束时间】'+StopWork FROM tab b where a.Attendance_Users_ID=b.Attendance_Users_ID FOR XML PATH('') ),1,1,'' )
from tab a
group by
Attendance_Users_ID,Attendance_UserName