现在我有如下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
其实我是要做一个员工的交班的报表,要显示的是,每个员工在一个月的上下班时间交班情况,或许我的思路是有问题的,有没有更好的解决办法,请高手帮帮忙,谢谢!
create table he
(Attendance_Users_ID varchar(8),
日期 varchar(4),
Attendance_UserName varchar(8),
startWork varchar(8),
StopWork varchar(8))insert into he
select 'A001', '17', 'admin', '8:30', '21:45' union all
select 'A001', '16', 'admin', '5:30', '12:23' union all
select 'A001', '15', 'admin', '12:30', '21:00' union all
select 'A002', '14', 'wilm', '19:30', '23:45' union all
select 'A002', '13', 'wilm', '07:30', '13:25'select * from he
/*
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(5 row(s) affected)
*/
declare @sql varchar(6000)select @sql='
with t as
(select row_number() over(partition by Attendance_Users_ID order by 日期 desc) rn,*
from he
)
select t1.Attendance_Users_ID,t1.Attendance_UserName,'select @sql=@sql
+'isnull((select 日期 from t t2
where t2.Attendance_Users_ID=t1.Attendance_Users_ID
and t2.rn='+rtrim(t.rn)+'),'''') ''日期'+rtrim(t.rn)+''','
+'isnull((select startWork from t t2
where t2.Attendance_Users_ID=t1.Attendance_Users_ID
and t2.rn='+rtrim(t.rn)+'),'''') ''startWork'','
+'isnull((select StopWork from t t2
where t2.Attendance_Users_ID=t1.Attendance_Users_ID
and t2.rn='+rtrim(t.rn)+'),'''') ''StopWork'','
from
(select number 'rn'
from master.dbo.spt_values where type='P'
and number between 1 and
(select top 1 count(1) from he
group by Attendance_Users_ID
order by count(1) desc)
) tselect @sql=left(@sql,len(@sql)-1)
+' from t t1 group by t1.Attendance_Users_ID,t1.Attendance_UserName'exec(@sql)/*
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 15 12:30 21:00
A002 wilm 14 19:30 23:45 13 07:30 13:25 (2 row(s) affected)
*/