SQL 考情统计查询请教

Question

现有数据如下
工号  打卡时间
123  2011-10-1 08:00
123  2011-10-1 18:00
123  2011-10-1 12:00
234  2011-10-1 08:00
234  2011-10-1 18:00
123  2011-10-2 08:00
123  2011-10-2 18:00
234  2011-10-2 08:00
234  2011-10-2 18:00
。
。
。要求报表
 日期     工号 时间1 时间2 时间3
2011-10-1 123  08:00 18:00 12:00
2011-10-1 234  08:00 18:00 
2011-10-2 123  08:00 18:00
2011-10-2 234  08:00 18:00SQL 怎么写比较好！我用了最原始的方法，建个临时表，一条一条数据插进去。查一个月的数据要等很久很久
 

Answer 1

declare @sql varchar(8000)
set @sql = 'select convert(varchar(10),打卡时间,120) as 打卡时间 '
select @sql = @sql + ' , max(case px when ''' + ltrim(px) + ''' then ltrim(CONVERT(varchar(5) , 打卡时间, 108 )) else 0 end) [时间' + ltrim(px) + ']'
from (select distinct px from (select px=ROW_NUMBER()over(PARTITION by 工号,convert(varchar(10),打卡时间,120) order by getdate()),* from tb)t ) as a
set @sql = @sql + ' from  (select px=ROW_NUMBER()over(PARTITION by 工号,convert(varchar(10),打卡时间,120) order by getdate()),* from tb)t group by convert(varchar(10),打卡时间,120)'
--print(@sql) 
exec(@sql)

Answer 2

----------------------------------------------------------------
-- Author  :fredrickhu(小F，向高手学习)
-- Date    :2011-10-24 15:46:13
-- Version:
--      Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (Intel X86) 
-- Apr 22 2011 11:57:00 
-- Copyright (c) Microsoft Corporation
-- Enterprise Evaluation Edition on Windows NT 6.1 <X64> (Build 7600: ) (WOW64)
--
----------------------------------------------------------------
--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
go 
create table [tb]([工号] int,[打卡时间] datetime)
insert [tb]
select 123,'2011-10-1 08:00' union all
select 123,'2011-10-1 18:00' union all
select 123,'2011-10-1 12:00' union all
select 234,'2011-10-1 08:00' union all
select 234,'2011-10-1 18:00' union all
select 123,'2011-10-2 08:00' union all
select 123,'2011-10-2 18:00' union all
select 234,'2011-10-2 08:00' union all
select 234,'2011-10-2 18:00'
--------------开始查询--------------------------
declare @sql varchar(8000)
set @sql = 'select convert(varchar(10),打卡时间,120) as 打卡时间,工号 '
select @sql = @sql + ' , max(case ltrim(px) when ''' + ltrim(px) + ''' then CONVERT(varchar(5) , 打卡时间, 108 ) else '''' end) [时间' + ltrim(px) + ']'
from (select distinct px from (select px=ROW_NUMBER()over(PARTITION by 工号,convert(varchar(10),打卡时间,120) order by getdate()),* from tb)t ) as a
set @sql = @sql + ' from  (select px=ROW_NUMBER()over(PARTITION by 工号,convert(varchar(10),打卡时间,120) order by getdate()),* from tb)t group by convert(varchar(10),打卡时间,120),工号'
--print(@sql) 
exec(@sql)
----------------结果----------------------------
/* 打卡时间       工号          时间1   时间2   时间3
---------- ----------- ----- ----- -----
2011-10-01 123         08:00 18:00 12:00
2011-10-02 123         08:00 18:00 
2011-10-01 234         08:00 18:00 
2011-10-02 234         08:00 18:00 (4 行受影响)
*/

Answer 3

SQL2000
use Tempdb
go
--> --> 
 
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([工号] int,[打卡时间] Datetime)
Insert #T
select 123,'2011-10-1 08:00' union all
select 123,'2011-10-1 18:00' union all
select 123,'2011-10-1 12:00' union all
select 234,'2011-10-1 08:00' union all
select 234,'2011-10-1 18:00' union all
select 123,'2011-10-2 08:00' union all
select 123,'2011-10-2 18:00' union all
select 234,'2011-10-2 08:00' union all
select 234,'2011-10-2 18:00'
Go
DECLARE @s NVARCHAR(4000),@i NVARCHAR(3)
SELECT TOP 1  @s='',@i=COUNT(*) from #T GROUP BY [工号],CONVERT(VARCHAR(10),[打卡时间],120) ORDER BY COUNT(*) DESC
WHILE @i>0
SELECT @s=N',[时间'+@i+']=max(case when row='+@i+N' then convert(varchar(5),[打卡时间],8) else ''''end)'+@s,@i=@i-1
EXEC(N'SELECT [工号],CONVERT(VARCHAR(10),[打卡时间],120)AS 日期'+@s+N'
FROM (SELECT *
,row=(SELECT COUNT(1) FROM #T WHERE 工号=a.工号 AND CONVERT(VARCHAR(10),a.[打卡时间],120)=CONVERT(VARCHAR(10),[打卡时间],120) AND [打卡时间]<=a.[打卡时间])
FROM #T AS a)t
GROUP BY [工号],CONVERT(VARCHAR(10),[打卡时间],120) order by 1,2')/*工号 日期 时间1 时间2 时间3
123 2011-10-01 08:00 12:00 18:00
123 2011-10-02 08:00 18:00
234 2011-10-01 08:00 18:00
234 2011-10-02 08:00 18:00
*/

Answer 4

SQL2005DECLARE @s NVARCHAR(4000),@i NVARCHAR(3)
SELECT TOP 1  @s='',@i=COUNT(*) from #T GROUP BY [工号],CONVERT(VARCHAR(10),[打卡时间],120) ORDER BY COUNT(*) DESC
WHILE @i>0
SELECT @s=N',['+@i+']'+@s,@i=@i-1
SET @s=STUFF(@s,1,1,'')EXEC(
N'SELECT *
FROM 
(SELECT [工号],convert(varchar(5),[打卡时间],8) as 时间,CONVERT(VARCHAR(10),[打卡时间],120) as 日期,row=row_number()over(partition by [工号],CONVERT(VARCHAR(10),[打卡时间],120)  order by [打卡时间])
FROM #T AS a)t
pivot
(max(时间) for row in('+@s+'))as b')/*工号 日期 1 2 3
123 2011-10-01 08:00 12:00 18:00
234 2011-10-01 08:00 18:00 NULL
123 2011-10-02 08:00 18:00 NULL
234 2011-10-02 08:00 18:00 NULL
*/