declare @t table(id int,name varchar(10),no varchar(10),kqdate datetime)
insert into @t select 129090051,'张三','00000001','2006-04-08 17:23:30.000'
insert into @t select 123322444,'李四','00000002','2006-04-08 20:25:23.000'
insert into @t select 123545555,'张三','00000001','2006-04-09 08:09:06.000'
insert into @t select 134568523,'张三','00000001','2006-04-09 12:02:55.000'
insert into @t select 135200234,'张三','00000001','2006-04-09 12:27:13.000'
insert into @t select 123012352,'李四','00000002','2006-04-09 12:38:14.000'
insert into @t select 129231520,'张三','00000001','2006-04-09 17:15:51.000'
insert into @t select 129345879,'李四','00000002','2006-04-09 17:30:48.000'
insert into @t select 129563789,'张三','00000001','2006-04-09 20:26:01.000'
insert into @t select 134658979,'李四','00000002','2006-04-10 08:10:03.000'
insert into @t select 134652389,'李四','00000002','2006-04-10 12:01:29.000'
insert into @t select 134789699,'李四','00000002','2006-04-10 12:22:05.000'
insert into @t select 134567899,'李四','00000002','2006-04-10 17:04:30.000'select
name,
no,
[date],
t1 =max(case num when 1 then time end),
t2 =max(case num when 2 then time end),
t3 =max(case num when 3 then time end),
t4 =max(case num when 4 then time end),
t5 =max(case num when 5 then time end),
t6 =max(case num when 6 then time end),
t7 =max(case num when 7 then time end),
t8 =max(case num when 8 then time end),
t9 =max(case num when 9 then time end),
t10=max(case num when 10 then time end)
from
(select
name,
no,
convert(char(10),kqdate,120) [date],
convert(char(8) ,kqdate,108) [time],
(select count(*) from @t where no=a.no and datediff(dd,kqdate,a.kqdate)=0 and kqdate<=a.kqdate) num
from
@t a) b
group by
name,no,[date]
order by
[date],no/*
name no date t1 t2 t3 t4 t5 t6 t7 t8 t9 t10
---------- ---------- ---------- -------- -------- -------- -------- -------- -------- -------- -------- -------- --------
张三 00000001 2006-04-08 17:23:30 NULL NULL NULL NULL NULL NULL NULL NULL NULL
李四 00000002 2006-04-08 20:25:23 NULL NULL NULL NULL NULL NULL NULL NULL NULL
张三 00000001 2006-04-09 08:09:06 12:02:55 12:27:13 17:15:51 20:26:01 NULL NULL NULL NULL NULL
李四 00000002 2006-04-09 12:38:14 17:30:48 NULL NULL NULL NULL NULL NULL NULL NULL
李四 00000002 2006-04-10 08:10:03 12:01:29 12:22:05 17:04:30 NULL NULL NULL NULL NULL NULL
*/
insert into @t select 129090051,'张三','00000001','2006-04-08 17:23:30.000'
insert into @t select 123322444,'李四','00000002','2006-04-08 20:25:23.000'
insert into @t select 123545555,'张三','00000001','2006-04-09 08:09:06.000'
insert into @t select 134568523,'张三','00000001','2006-04-09 12:02:55.000'
insert into @t select 135200234,'张三','00000001','2006-04-09 12:27:13.000'
insert into @t select 123012352,'李四','00000002','2006-04-09 12:38:14.000'
insert into @t select 129231520,'张三','00000001','2006-04-09 17:15:51.000'
insert into @t select 129345879,'李四','00000002','2006-04-09 17:30:48.000'
insert into @t select 129563789,'张三','00000001','2006-04-09 20:26:01.000'
insert into @t select 134658979,'李四','00000002','2006-04-10 08:10:03.000'
insert into @t select 134652389,'李四','00000002','2006-04-10 12:01:29.000'
insert into @t select 134789699,'李四','00000002','2006-04-10 12:22:05.000'
insert into @t select 134567899,'李四','00000002','2006-04-10 17:04:30.000'select
name,
no,
[date],
t1 =max(case num when 1 then time end),
t2 =max(case num when 2 then time end),
t3 =max(case num when 3 then time end),
t4 =max(case num when 4 then time end),
t5 =max(case num when 5 then time end),
t6 =max(case num when 6 then time end),
t7 =max(case num when 7 then time end),
t8 =max(case num when 8 then time end),
t9 =max(case num when 9 then time end),
t10=max(case num when 10 then time end)
from
(select
name,
no,
convert(char(10),kqdate,120) [date],
convert(char(8) ,kqdate,108) [time],
(select count(*) from @t where no=a.no and datediff(dd,kqdate,a.kqdate)=0 and kqdate<=a.kqdate) num
from
@t a) b
group by
name,no,[date]
order by
[date],no/*
name no date t1 t2 t3 t4 t5 t6 t7 t8 t9 t10
---------- ---------- ---------- -------- -------- -------- -------- -------- -------- -------- -------- -------- --------
张三 00000001 2006-04-08 17:23:30 NULL NULL NULL NULL NULL NULL NULL NULL NULL
李四 00000002 2006-04-08 20:25:23 NULL NULL NULL NULL NULL NULL NULL NULL NULL
张三 00000001 2006-04-09 08:09:06 12:02:55 12:27:13 17:15:51 20:26:01 NULL NULL NULL NULL NULL
李四 00000002 2006-04-09 12:38:14 17:30:48 NULL NULL NULL NULL NULL NULL NULL NULL
李四 00000002 2006-04-10 08:10:03 12:01:29 12:22:05 17:04:30 NULL NULL NULL NULL NULL NULL
*/
insert into @t select 129090051,'张三','00000001','2006-04-08 17:23:30.000'
insert into @t select 123322444,'李四','00000002','2006-04-08 20:25:23.000'
insert into @t select 123545555,'张三','00000001','2006-04-09 08:09:06.000'
insert into @t select 134568523,'张三','00000001','2006-04-09 12:02:55.000'
insert into @t select 135200234,'张三','00000001','2006-04-09 12:27:13.000'
insert into @t select 123012352,'李四','00000002','2006-04-09 12:38:14.000'
insert into @t select 129231520,'张三','00000001','2006-04-09 17:15:51.000'
insert into @t select 129345879,'李四','00000002','2006-04-09 17:30:48.000'
insert into @t select 129563789,'张三','00000001','2006-04-09 20:26:01.000'
insert into @t select 134658979,'李四','00000002','2006-04-10 08:10:03.000'
insert into @t select 134652389,'李四','00000002','2006-04-10 12:01:29.000'
insert into @t select 134789699,'李四','00000002','2006-04-10 12:22:05.000'
insert into @t select 134567899,'李四','00000002','2006-04-10 17:04:30.000'select
identity(int,1,1) id,
name,
no,
[date],
t1 =max(case num when 1 then time end),
t2 =max(case num when 2 then time end),
t3 =max(case num when 3 then time end),
t4 =max(case num when 4 then time end),
t5 =max(case num when 5 then time end),
t6 =max(case num when 6 then time end),
t7 =max(case num when 7 then time end),
t8 =max(case num when 8 then time end),
t9 =max(case num when 9 then time end),
t10=max(case num when 10 then time end)
into #
from
(select
name,
no,
convert(char(10),kqdate,120) [date],
convert(char(5) ,kqdate,108) [time],
(select count(*) from @t where no=a.no and datediff(dd,kqdate,a.kqdate)=0 and kqdate<=a.kqdate) num
from
@t a) b
group by
name,no,[date]
order by
[date],noselect * from #/*
id name no date t1 t2 t3 t4 t5 t6 t7 t8 t9 t10
----------- ---------- ---------- ---------- ----- ----- ----- ----- ----- ----- ----- ----- ----- -----
1 张三 00000001 2006-04-08 17:23 NULL NULL NULL NULL NULL NULL NULL NULL NULL
2 李四 00000002 2006-04-08 20:25 NULL NULL NULL NULL NULL NULL NULL NULL NULL
3 张三 00000001 2006-04-09 08:09 12:02 12:27 17:15 20:26 NULL NULL NULL NULL NULL
4 李四 00000002 2006-04-09 12:38 17:30 NULL NULL NULL NULL NULL NULL NULL NULL
5 李四 00000002 2006-04-10 08:10 12:01 12:22 17:04 NULL NULL NULL NULL NULL NULL
*/
insert into @t select 129090051,'张三','00000001','2006-04-08 17:23:30.000'
insert into @t select 123322444,'李四','00000002','2006-04-08 20:25:23.000'
insert into @t select 123545555,'张三','00000001','2006-04-09 08:09:06.000'
insert into @t select 134568523,'张三','00000001','2006-04-09 12:02:55.000'
insert into @t select 135200234,'张三','00000001','2006-04-09 12:27:13.000'
insert into @t select 123012352,'李四','00000002','2006-04-09 12:38:14.000'
insert into @t select 129231520,'张三','00000001','2006-04-09 17:15:51.000'
insert into @t select 129345879,'李四','00000002','2006-04-09 17:30:48.000'
insert into @t select 129563789,'张三','00000001','2006-04-09 20:26:01.000'
insert into @t select 134658979,'李四','00000002','2006-04-10 08:10:03.000'
insert into @t select 134652389,'李四','00000002','2006-04-10 12:01:29.000'
insert into @t select 134789699,'李四','00000002','2006-04-10 12:22:05.000'
insert into @t select 134567899,'李四','00000002','2006-04-10 17:04:30.000'select
identity(int,1,1) id,
name,
no,
[date],
t1 =max(case num when 1 then time end),
t2 =max(case num when 2 then time end),
t3 =max(case num when 3 then time end),
t4 =max(case num when 4 then time end),
t5 =max(case num when 5 then time end),
t6 =max(case num when 6 then time end),
t7 =max(case num when 7 then time end),
t8 =max(case num when 8 then time end),
t9 =max(case num when 9 then time end),
t10=max(case num when 10 then time end)
into #
from
(select
name,
no,
convert(char(10),kqdate,120) [date],
convert(char(5) ,kqdate,108) [time],
(select count(*) from @t where no=a.no and datediff(dd,kqdate,a.kqdate)=0 and kqdate<=a.kqdate) num
from
@t a) b
group by
name,no,[date]
order by
[date],noselect * from #/*
id name no date t1 t2 t3 t4 t5 t6 t7 t8 t9 t10
----------- ---------- ---------- ---------- ----- ----- ----- ----- ----- ----- ----- ----- ----- -----
1 张三 00000001 2006-04-08 17:23 NULL NULL NULL NULL NULL NULL NULL NULL NULL
2 李四 00000002 2006-04-08 20:25 NULL NULL NULL NULL NULL NULL NULL NULL NULL
3 张三 00000001 2006-04-09 08:09 12:02 12:27 17:15 20:26 NULL NULL NULL NULL NULL
4 李四 00000002 2006-04-09 12:38 17:30 NULL NULL NULL NULL NULL NULL NULL NULL
5 李四 00000002 2006-04-10 08:10 12:01 12:22 17:04 NULL NULL NULL NULL NULL NULL
*/
--测试表及数据
create table #(id int,name varchar(20),no char(8),kqdate datetime)
insert into #
select 129090051, '张三', '00000001', '2006-04-08 17:23:30.000' union
select 123322444, '李四', '00000002', '2006-04-08 20:25:23.000' union
select 123545555 , '张三', '00000001', '2006-04-09 08:09:06.000' union
select 134568523, '张三', '00000001', '2006-04-09 12:02:55.000' union
select 135200234, '张三', '00000001', '2006-04-09 12:27:13.000' union
select 123012352, '李四', '00000002', '2006-04-09 12:38:14.000' union
select 129231520, '张三', '00000001', '2006-04-09 17:15:51.000' union
select 129345879, '李四', '00000002', '2006-04-09 17:30:48.000' union
select 129563789, '张三', '00000001', '2006-04-09 20:26:01.000' union
select 134658979, '李四', '00000002', '2006-04-10 08:10:03.000' union
select 134652389, '李四', '00000002', '2006-04-10 12:01:29.000' union
select 134789699, '李四', '00000002', '2006-04-10 12:22:05.000' union
select 134567899, '李四', '00000002', '2006-04-10 17:04:30.000' --临时辅助表
select id =identity(int,1,1),convert(varchar(5),kqdate,108) as ti into #temp
from #
group by convert(varchar(5),kqdate,108)
--查询
declare @sql varchar(8000)
select @sql=''
select @sql=@sql+',[t'+convert(varchar,id)+']=max(case convert(varchar(5),kqdate,108) when '''+ti+''' then convert(varchar(5),kqdate,108) end)'
from #temp
exec('select [name],convert(varchar(10),kqdate,120) as riqi'+@sql+' from # group by convert(varchar(10),kqdate,120),[name] order by convert(varchar(10),kqdate,120),[name]')
--删除辅助表及测试表
drop table #temp
drop table #/*结果
李四 2006-04-08 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 20:25 NULL
张三 2006-04-08 NULL NULL NULL NULL NULL NULL NULL NULL NULL 17:23 NULL NULL NULL
李四 2006-04-09 NULL NULL NULL NULL NULL NULL 12:38 NULL NULL NULL 17:30 NULL NULL
张三 2006-04-09 08:09 NULL NULL 12:02 NULL 12:27 NULL NULL 17:15 NULL NULL NULL 20:26
李四 2006-04-10 NULL 08:10 12:01 NULL 12:22 NULL NULL 17:04 NULL NULL NULL NULL NULL*/
你们好,我先试一下。有什么情况马上跟你们联系!Thanks!
--建立測試環境
Create Table datakq
(id int,
name Nvarchar(10),
no Varchar(8),
kqdate datetime)
--插入數據
Insert datakq Select 129090051, N'张三', '00000001', '2006-04-08 17:23:30.000'
Union All Select 123322444, N'李四', '00000002', '2006-04-08 20:25:23.000'
Union All Select 123545555, N'张三', '00000001', '2006-04-09 08:09:06.000'
Union All Select 134568523, N'张三', '00000001', '2006-04-09 12:02:55.000'
Union All Select 135200234, N'张三', '00000001', '2006-04-09 12:27:13.000'
Union All Select 123012352, N'李四', '00000002', '2006-04-09 12:38:14.000'
Union All Select 129231520, N'张三', '00000001', '2006-04-09 17:15:51.000'
Union All Select 129345879, N'李四', '00000002', '2006-04-09 17:30:48.000'
Union All Select 129563789, N'张三', '00000001', '2006-04-09 20:26:01.000'
Union All Select 134658979, N'李四', '00000002', '2006-04-10 08:10:03.000'
Union All Select 134652389, N'李四', '00000002', '2006-04-10 12:01:29.000'
Union All Select 134789699, N'李四', '00000002', '2006-04-10 12:22:05.000'
Union All Select 134567899, N'李四', '00000002', '2006-04-10 17:04:30.000'
--測試
Select
name,
no As num,
riqi,
Max(Case Count When 1 Then t End) As t1,
Max(Case Count When 2 Then t End) As t2,
Max(Case Count When 3 Then t End) As t3,
Max(Case Count When 4 Then t End) As t4,
Max(Case Count When 5 Then t End) As t5,
Max(Case Count When 6 Then t End) As t6,
Max(Case Count When 7 Then t End) As t7,
Max(Case Count When 8 Then t End) As t8,
Max(Case Count When 9 Then t End) As t9,
Max(Case Count When 10 Then t End) As t10
from
(
Select
name,
no,
Convert(Varchar(10),kqdate,120) As riqi,
Convert(Varchar,kqdate,108) As t,
(Select Count(*) from datakq Where Name=A.Name And DateDiff(dd,kqdate,A.kqdate)=0 And DateDiff(ss,kqdate,A.kqdate)>=0) As Count
from datakq A) B
Group By name,no,riqi
Order By riqi,no
--刪除測試環境
Drop Table datakq
--結果
/*
name num riqi t1 t2 t3 t4 t5 t6 t7 t8 t9 t10
张三 00000001 2006-04-08 17:23:30 NULL NULL NULL NULL NULL NULL NULL NULL NULL
李四 00000002 2006-04-08 20:25:23 NULL NULL NULL NULL NULL NULL NULL NULL NULL
张三 00000001 2006-04-09 08:09:06 12:02:55 12:27:13 17:15:51 20:26:01 NULL NULL NULL NULL NULL
李四 00000002 2006-04-09 12:38:14 17:30:48 NULL NULL NULL NULL NULL NULL NULL NULL
李四 00000002 2006-04-10 08:10:03 12:01:29 12:22:05 17:04:30 NULL NULL NULL NULL NULL NULL
*/
你们好,我试过了。rivery(river)的语句执行起来稍慢点,libin_ftsafe(子陌红尘)稍快点。但是由于原来表的数据行太大,有上百万行,如果一下执行可能会导致服务器很慢。我想能分批执行吧!比如按旧表的id排列顺序,第一次执行到哪个id号运行完后,保存这个id号,下一次执行则从此id开始执行下一个。我想可能需用游标功能!请高手帮忙!