表：KaoQin
- SQL code
U_ID Zaoshang Wanshang 1001 2012-9-1 08:39:00.000 2012-9-1 18:36:00.0001001 2012-9-2 08:20:00.000 2012-9-2 18:46:00.0001001 2012-9-3 08:26:00.000 2012-9-3 17:46:00.0001002 2012-9-1 08:22:00.000 2012-9-1 18:12:00.0001002 2012-9-2 08:22:00.000 2012-9-2 18:23:00.0001002 2012-9-3 08:15:00.000 2012-9-3 18:26:00.000......
要得到 一个月 每天 的 上下班 时间
如下样式：
- SQL code
U_ID 1 2 3 4 5 6... 301001 08:20-18:36 08:20-18:46 08:26-17:461002 08:22-18:12 08:22-18:23 08:15-18:26
搞了很久实在搞不定 谢谢解答了
------解决方案--------------------
脚本如下：
- SQL code
CREATE TABLE #Temp( [ID] [int] IDENTITY(1,1) NOT NULL, [U_ID] [nvarchar](50) NULL, [zaoshang] datetime NULL, [wanshang]datetime NULL )insert into #tempselect '1001','2012-9-1 08:39:00.000','2012-9-1 18:36:00.000' union allselect '1001','2012-9-2 08:20:00.000','2012-9-2 18:46:00.000' union allselect '1001','2012-9-3 08:26:00.000','2012-9-3 17:46:00.000' union allselect '1002','2012-9-1 08:22:00.000','2012-9-1 18:12:00.000' union allselect '1002','2012-9-2 08:22:00.000','2012-9-2 18:23:00.000' union allselect '1002','2012-9-3 08:15:00.000','2012-9-3 18:26:00.000';with abc as (select U_ID,cast (convert(varchar(10),zaoshang,8)+'-'+convert(varchar(10),wanshang,8) as nvarchar(20)) shijian, day(zaoshang) as riqi from #temp )select U_ID,[1],[2],[3],[4]from abcpivot(max(shijian) for [riqi] in ([1],[2],[3],[4])) as pvt drop table #Temp
------解决方案--------------------
- SQL code
IF (OBJECT_ID('TBL')) IS NOT NULL DROP TABLE TBLGOCREATE TABLE TBL(U_ID INT,Zaoshang DATETIME,Wanshang datetime)INSERT INTO TBLSELECT 1001,'2012-9-1 08:39:00.000','2012-9-1 18:36:00.000' UNIONSELECT 1001,'2012-9-2 08:20:00.000','2012-9-2 18:46:00.000' UNIONSELECT 1001,'2012-9-3 08:26:00.000','2012-9-3 17:46:00.000' UNIONSELECT 1002,'2012-9-1 08:22:00.000','2012-9-1 18:12:00.000' UNIONSELECT 1002,'2012-9-2 08:22:00.000','2012-9-2 18:23:00.000' UNIONSELECT 1002,'2012-9-3 08:15:00.000','2012-9-3 18:26:00.000'GOWITH CTE AS(select U_ID,datepart(dd,Zaoshang) work_day,convert(nvarchar(16),Zaoshang,20)+'&'+convert(nvarchar(16),Wanshang,20) work_Time FROM TBL)SELECT U_ID,[1],[2],[3] FROM CTE pivot(MAX(work_time) FOR work_day IN ([1],[2],[3])) AS T