用户表：users
id name
1 张三
2 李四
3 王五
签到记录表：sign_record
id userid date
1 1 2012-10-1 7:14:03.000
2 2 2012-10-2 7:14:10.000
3 1 2012-10-4 8:14:59.000
4 1 2012-10-3 8:15:02.000
5 3 2012-10-3 7:15:08.000
查询结果：
显示从1号到最后一天的所有人的考勤情况：1、y为正常；2、n为当天没有签到；3、超过8点签到的记录迟到分钟数；
name 1 2 3 4 5 6 ... 31
张三 y n 15 n n n n
李四 n y n n n n n
王五 n n y 14 n n n
------解决方案--------------------
行转列，
select userid, (case when day(date) = 1 and time(date)<=8 then 'y' when when day(date) = 1 and time(date)>8 then time(date)-8 when day(date) <> 1 then 'n') as "1",
.
.
.--一直写到31号
from sign_record
group by userid;
------解决方案--------------------
- SQL code
--查询结果：--显示从1号到最后一天的所有人的考勤情况：1、y为正常；2、n为当天没有签到；3、超过8点签到的记录迟到分钟数；--name 1 2 3 4 5 6 ... 31--张三 y n 15 n n n n--李四 n y n n n n n--王五 n n y 14 n n nUSE testgo--if object_id('users') is not null -- drop table users--Go--Create table users([id] smallint,[name] nvarchar(2))--Insert into users--Select 1,N'张三'--Union all Select 2,N'李四'--Union all Select 3,N'王五'--------if object_id('sign_record') is not null -- drop table sign_record--Go--Create table sign_record([id] smallint,[userid] smallint,[date] datetime)--Insert into sign_record--Select 1,1,'2012-10-1 7:14:03.000'--Union all Select 2,2,'2012-10-2 7:14:10.000'--Union all Select 3,1,'2012-10-4 8:14:59.000'--Union all Select 4,1,'2012-10-3 8:15:02.000'--Union all Select 5,3,'2012-10-3 7:15:08.000';WITH Result_tmp AS ( select b.id ,b.name ,Convert(nvarchar(50),datediff(mi,convert(nvarchar(10),a.date,120)+' 08:00:00',a.date)) as datediff ,DAY(a.date) AS mark from sign_record AS a INNER JOIN users AS b ON a.userid=b.id),Result as ( select id ,name ,Case when datediff<=0 then 'y' Else datediff End as datediff ,mark from Result_tmp)SELECT name,isnull([1],'n') As [1],isnull([2],'n') As [2],isnull([3],'n') As [3],isnull([4],'n') As [4],isnull([5],'n') As [5],isnull([6],'n') As [6],isnull([7],'n') As [7],isnull([8],'n') As [8],isnull([9],'n') As [9],isnull([10],'n') As [10],isnull([11],'n') As [11],isnull([12],'n') As [12],isnull([13],'n') As [13],isnull([14],'n') As [14],isnull([15],'n') As [15],isnull([16],'n') As [16],isnull([17],'n') As [17],isnull([18],'n') As [18],isnull([19],'n') As [19],isnull([20],'n') As [20],isnull([21],'n') As [21],isnull([22],'n') As [22],isnull([23],'n') As [23],isnull([24],'n') As [24],isnull([25],'n') As [25],isnull([26],'n') As [26],isnull([27],'n') As [27],isnull([28],'n') As [28],isnull([29],'n') As [29],isnull([30],'n') As [30],isnull([31],'n') As [31] FROM Result PIVOT( MAX(datediff) FOR mark IN([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13],[14],[15],[16],[17],[18],[19],[20],[21],[22],[23],[24],[25],[26],[27],[28],[29],[30],[31]) )p Order by idGo