解决方案 »
- 大家有没有碰到过复制很多脚本给isqlw,一起执行,结果漏掉几行未执行的情况?
- 郁闷了!!! 简单的删除表操作
- 在删除索引时遇到的问题
- 求一sql语句,十万火急********************************
- 请教convert(varchar(20),@opernum,111)什么意思?
- 统计用户发布记录数并且排序
- 大量数据中多列中如何快速搜索相应数据
- SQL变态笔试题(续。。。)求解。。。高手来!~~
- 用VF的朋友请进来,PLMM有求,,在线等待..
- 我想知道在vb中怎样向sql server的数据库中输入图片,并且此时我sql server中的数据类型是什么?最好是两种以上的方法,越简单越好,拜托了!
- 请问一个SQL语句该怎么写
- 求帮忙,两表关联
CREATE TABLE #Temp(
[ID] [int] IDENTITY(1,1) NOT NULL,
[U_ID] [nvarchar](50) NULL,
[zaoshang] datetime NULL,
[wanshang]datetime NULL
)insert into #temp
select '1001','2012-9-1 08:39:00.000','2012-9-1 18:36:00.000' union all
select '1001','2012-9-2 08:20:00.000','2012-9-2 18:46:00.000' union all
select '1001','2012-9-3 08:26:00.000','2012-9-3 17:46:00.000' union all
select '1002','2012-9-1 08:22:00.000','2012-9-1 18:12:00.000' union all
select '1002','2012-9-2 08:22:00.000','2012-9-2 18:23:00.000' union all
select '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 abc
pivot(max(shijian) for [riqi] in ([1],[2],[3],[4])) as pvt
drop table #Temp
--(6 行受影响)--(2 行受影响)U_ID 1 2 3 4
1001 08:39:00-18:36:00 08:20:00-18:46:00 08:26:00-17:46:00 NULL
1002 08:22:00-18:12:00 08:22:00-18:23:00 08:15:00-18:26:00 NULL
GO
CREATE TABLE TBL
(
U_ID INT,
Zaoshang DATETIME,
Wanshang datetime
)
INSERT INTO TBL
SELECT 1001,'2012-9-1 08:39:00.000','2012-9-1 18:36:00.000' UNION
SELECT 1001,'2012-9-2 08:20:00.000','2012-9-2 18:46:00.000' UNION
SELECT 1001,'2012-9-3 08:26:00.000','2012-9-3 17:46:00.000' UNION
SELECT 1002,'2012-9-1 08:22:00.000','2012-9-1 18:12:00.000' UNION
SELECT 1002,'2012-9-2 08:22:00.000','2012-9-2 18:23:00.000' UNION
SELECT 1002,'2012-9-3 08:15:00.000','2012-9-3 18:26:00.000'GO
WITH 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