表 A idbo dd Sdate stime
000008 1 2007/09/26 08:00:00
000008 1 2007/09/26 12:30:00
000008 1 2007/09/26 13:30:00
000008 1 2007/09/26 17:00:00
000011 1 2007/09/26 07:30:00
000011 1 2007/09/26 11:30:00
000011 1 2007/09/26 11:45:00
000011 1 2007/09/26 16:30:00
000011 1 2007/09/26 16:45:00
000011 1 2007/09/26 21:00:00
000008 1 2007/09/27 08:20:07
000008 1 2007/09/27 12:20:00
000008 1 2007/09/27 13:00:00
000008 1 2007/09/27 17:30:00
得到查询结果
idno Sdate T1 T2 T3 T4 t5 T6 T7 T8
000008 2007/09/26 08:00:00 12:30:00 13:30:00 17:00:00
000011 2007/09/26 07:30:00 11:30:00 11:45:00 16:30:00 16:45:00 21:00:00
000008 2007/09/27 08:20:07 12:20:00 13:00:00 17:30:00
000008 1 2007/09/26 08:00:00
000008 1 2007/09/26 12:30:00
000008 1 2007/09/26 13:30:00
000008 1 2007/09/26 17:00:00
000011 1 2007/09/26 07:30:00
000011 1 2007/09/26 11:30:00
000011 1 2007/09/26 11:45:00
000011 1 2007/09/26 16:30:00
000011 1 2007/09/26 16:45:00
000011 1 2007/09/26 21:00:00
000008 1 2007/09/27 08:20:07
000008 1 2007/09/27 12:20:00
000008 1 2007/09/27 13:00:00
000008 1 2007/09/27 17:30:00
得到查询结果
idno Sdate T1 T2 T3 T4 t5 T6 T7 T8
000008 2007/09/26 08:00:00 12:30:00 13:30:00 17:00:00
000011 2007/09/26 07:30:00 11:30:00 11:45:00 16:30:00 16:45:00 21:00:00
000008 2007/09/27 08:20:07 12:20:00 13:00:00 17:30:00
set @sql = 'select idno,Sdate'
select @sql = @sql + ' , max(case px when ''' + cast(px as varchar) + ''' then stime else '' '' end) [T' + cast(px as varchar) + ']'
from (select distinct px from (select px=(select count(1) from a where idbo=t.idbo and Sdate = t.Sdate and stime<t.stime)+1 , * from a t) m) as a
set @sql = @sql + ' from (select px=(select count(1) from a where idbo=t.idbo and Sdate = t.Sdate and stime<t.stime)+1 , * from a t) m group by idno,Sdate'
exec(@sql)
insert into tb values('000008', 1, '2007/09/26', '08:00:00')
insert into tb values('000008', 1, '2007/09/26', '12:30:00')
insert into tb values('000008', 1, '2007/09/26', '13:30:00')
insert into tb values('000008', 1, '2007/09/26', '17:00:00')
insert into tb values('000011', 1, '2007/09/26', '07:30:00')
insert into tb values('000011', 1, '2007/09/26', '11:30:00')
insert into tb values('000011', 1, '2007/09/26', '11:45:00')
insert into tb values('000011', 1, '2007/09/26', '16:30:00')
insert into tb values('000011', 1, '2007/09/26', '16:45:00')
insert into tb values('000011', 1, '2007/09/26', '21:00:00')
insert into tb values('000008', 1, '2007/09/27', '08:20:07')
insert into tb values('000008', 1, '2007/09/27', '12:20:00')
insert into tb values('000008', 1, '2007/09/27', '13:00:00')
insert into tb values('000008', 1, '2007/09/27', '17:30:00')
go
declare @sql varchar(8000)
set @sql = 'select idbo,Sdate'
select @sql = @sql + ' , max(case px when ''' + cast(px as varchar) + ''' then stime else '' '' end) [T' + cast(px as varchar) + ']'
from (select distinct px from (select px=(select count(1) from tb where idbo=t.idbo and Sdate = t.Sdate and stime<t.stime)+1 , * from tb t) m) as a
set @sql = @sql + ' from (select px=(select count(1) from tb where idbo=t.idbo and Sdate = t.Sdate and stime<t.stime)+1 , * from tb t) m group by idbo,Sdate'
exec(@sql)/*
idbo Sdate T1 T2 T3 T4 T5 T6
---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
000008 2007/09/26 08:00:00 12:30:00 13:30:00 17:00:00
000011 2007/09/26 07:30:00 11:30:00 11:45:00 16:30:00 16:45:00 21:00:00
000008 2007/09/27 08:20:07 12:20:00 13:00:00 17:30:00
*/drop table tb/*
总出货数
-----------
4(所影响的行数为 1 行)*/
insert into tb values('000008', 1, '2007/09/26', '08:00:00')
insert into tb values('000008', 1, '2007/09/26', '12:30:00')
insert into tb values('000008', 1, '2007/09/26', '13:30:00')
insert into tb values('000008', 1, '2007/09/26', '17:00:00')
insert into tb values('000011', 1, '2007/09/26', '07:30:00')
insert into tb values('000011', 1, '2007/09/26', '11:30:00')
insert into tb values('000011', 1, '2007/09/26', '11:45:00')
insert into tb values('000011', 1, '2007/09/26', '16:30:00')
insert into tb values('000011', 1, '2007/09/26', '16:45:00')
insert into tb values('000011', 1, '2007/09/26', '21:00:00')
insert into tb values('000008', 1, '2007/09/27', '08:20:07')
insert into tb values('000008', 1, '2007/09/27', '12:20:00')
insert into tb values('000008', 1, '2007/09/27', '13:00:00')
insert into tb values('000008', 1, '2007/09/27', '17:30:00')
go
declare @sql varchar(8000)
set @sql = 'select idbo,Sdate'
select @sql = @sql + ' , max(case px when ''' + cast(px as varchar) + ''' then stime else '' '' end) [T' + cast(px as varchar) + ']'
from (select distinct px from (select px=(select count(1) from tb where idbo=t.idbo and Sdate = t.Sdate and stime<t.stime)+1 , * from tb t) m) as a
set @sql = @sql + ' from (select px=(select count(1) from tb where idbo=t.idbo and Sdate = t.Sdate and stime<t.stime)+1 , * from tb t) m group by idbo,Sdate'
exec(@sql)/*
idbo Sdate T1 T2 T3 T4 T5 T6
---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
000008 2007/09/26 08:00:00 12:30:00 13:30:00 17:00:00
000011 2007/09/26 07:30:00 11:30:00 11:45:00 16:30:00 16:45:00 21:00:00
000008 2007/09/27 08:20:07 12:20:00 13:00:00 17:30:00
*/drop table tb
Set @sql=''
Select @sql=@sql+',Max(Case When Pid='''+rtrim(Pid)+''' Then stime Else '''' End) As [T'+rtrim(Pid)+']'
From (Select idno,Sdate,stime,Pid=(Select Count(1) From A Where idno=T.idno And Sdate=T.Sdate And stime<=T.stime) From A As T) As TT
Group By Pid
Exec('Select idno,Sdate'+@sql+'From (Select idno,Sdate,stime,
Pid=(Select Count(1)
From A
Where idno=T.idno And Sdate=T.Sdate And stime<=T.stime)
From A As T) As TT Group By idno,Sdate')
Create Table A(idno varchar(10),dd int,Sdate varchar(10),stime varchar(10))
Insert A Select '000008',1,'2007/09/26','08:00:00'
Union All Select '000008',1,'2007/09/26','12:30:00'
Union All Select '000008',1,'2007/09/26','13:30:00'
Union All Select '000008',1,'2007/09/26','17:00:00'
Union All Select '000011',1,'2007/09/26','07:30:00'
Union All Select '000011',1,'2007/09/26','11:30:00'
Union All Select '000011',1,'2007/09/26','11:45:00'
Union All Select '000011',1,'2007/09/26','16:30:00'
Union All Select '000011',1,'2007/09/26','16:45:00'
Union All Select '000011',1,'2007/09/26','21:00:00'
Union All Select '000008',1,'2007/09/27','08:20:07'
Union All Select '000008',1,'2007/09/27','12:20:00'
Union All Select '000008',1,'2007/09/27','13:00:00'
Union All Select '000008',1,'2007/09/27','17:30:00'---查询结果
Declare @sql varchar(8000)
Set @sql=''
Select @sql=@sql+',Max(Case When Pid='''+rtrim(Pid)+''' Then stime Else '''' End) As [T'+rtrim(Pid)+']'
From (Select idno,Sdate,stime,Pid=(Select Count(1) From A Where idno=T.idno And Sdate=T.Sdate And stime<=T.stime) From A As T) As TT
Group By Pid
Exec('Select idno,Sdate'+@sql+'
From (Select idno,Sdate,stime,
Pid=(Select Count(1)
From A
Where idno=T.idno And Sdate=T.Sdate And stime<=T.stime)
From A As T) As TT Group By idno,Sdate')
---清除测试环境
Drop Table A
---结果
/*
(所影响的行数为 14 行)idno Sdate T1 T2 T3 T4 T5 T6
---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
000008 2007/09/26 08:00:00 12:30:00 13:30:00 17:00:00
000011 2007/09/26 07:30:00 11:30:00 11:45:00 16:30:00 16:45:00 21:00:00
000008 2007/09/27 08:20:07 12:20:00 13:00:00 17:30:00 */
都是动态的,我们不知道的。
declare @sql varchar(8000)
set @sql = 'select idno,Sdate'
select @sql = @sql + ' , max(case px when ''' + cast(px as varchar) + ''' then stime else '' '' end) [T' + cast(px as varchar) + ']'
from (select distinct px from (select px=(select count(1) from a where idbo=t.idbo and Sdate = t.Sdate and stime<t.stime)+1 , * from a t) m) as a
set @sql = @sql + ' from (select px=(select count(1) from a where idbo=t.idbo and Sdate = t.Sdate and stime<t.stime)+1 , * from a t) m group by idno,Sdate'
exec(@sql)
拷贝到你的查询分析器里面运行一下就知道了.
Limpire:对啊,都是动态的嘛!
*/--原始数据:#A
create table #A(idbo varchar(6),dd int,Sdate varchar(10),stime varchar(8))
insert #A
select '000008',1,'2007/09/26','08:00:00' union all
select '000008',1,'2007/09/26','12:30:00' union all
select '000008',1,'2007/09/26','13:30:00' union all
select '000008',1,'2007/09/26','17:00:00' union all
select '000011',1,'2007/09/26','07:30:00' union all
select '000011',1,'2007/09/26','11:30:00' union all
select '000011',1,'2007/09/26','11:45:00' union all
select '000011',1,'2007/09/26','16:30:00' union all
select '000011',1,'2007/09/26','16:45:00' union all
select '000011',1,'2007/09/26','21:00:00' union all
select '000008',1,'2007/09/27','08:20:07' union all
select '000008',1,'2007/09/27','12:20:00' union all
select '000008',1,'2007/09/27','13:00:00' union all
select '000008',1,'2007/09/27','17:30:00'declare @Max int,@T varchar(10),@sql varchar(8000)
select top 1 @Max=count(1),@T=1 from #A group by idbo,Sdate order by count(1) desc
while @T<=@Max
select @sql=coalesce(@sql+',','select idbo,Sdate,')+'[T'+@T+']=max(case CT when '+@T+' then stime else '''' end)',@T=@T+1
set @sql = @sql+' from (select CT=(select count(1) from #A where idbo=a.idbo and Sdate=a.Sdate and stime<=a.stime), * from #A a) a group by idbo,Sdate'
exec (@sql)/*
得到查询结果
odbo Sdate T1 T2 T3 T4 T5 T6
000008 2007/09/26 08:00:00 12:30:00 13:30:00 17:00:00
000011 2007/09/26 07:30:00 11:30:00 11:45:00 16:30:00 16:45:00 21:00:00
000008 2007/09/27 08:20:07 12:20:00 13:00:00 17:30:00
*/--删除对象
drop table #A
(
idbo varchar(8),
dd int,
sdate datetime,
stime varchar(8)
)
goinsert into #temp
select '000008',1,'2007/09/26','08:00:00' union all
select '000008',1,'2007/09/26','12:30:00' union all
select '000008',1,'2007/09/26','13:30:00' union all
select '000008',1,'2007/09/26','17:00:00' union all
select '000011',1,'2007/09/26','07:30:00' union all
select '000011',1,'2007/09/26','11:30:00' union all
select '000011',1,'2007/09/26','11:45:00' union all
select '000011',1,'2007/09/26','16:30:00' union all
select '000011',1,'2007/09/26','16:45:00' union all
select '000011',1,'2007/09/26','21:00:00' union all
select '000008',1,'2007/09/27','08:20:07' union all
select '000008',1,'2007/09/27','12:20:00' union all
select '000008',1,'2007/09/27','13:00:00' union all
select '000008',1,'2007/09/27','17:30:00'
select A1.idbo,convert(varchar(10),A1.sdate,111) sdate,A1.stime T1,A2.stime T2
,A3.stime T3,A4.stime T4,A5.stime T5,A6.stime T6,A7.stime A7,A8.stime T8
from (
select idbo,sdate,min(stime) stime from #temp group by idbo,sdate
) A1 left join #temp A2
on A2.idbo=A1.idbo
and A2.sdate=A1.sdate
and A2.stime=(select min(stime) from #temp where idbo=A2.idbo and sdate=A2.sdate and stime>A1.stime)
left join #temp A3
on A3.idbo=A1.idbo
and A3.sdate=A1.sdate
and A3.stime=(select min(stime) from #temp where idbo=A3.idbo and sdate=A3.sdate and stime>A2.stime)
left join #temp A4
on A4.idbo=A1.idbo
and A4.sdate=A1.sdate
and A4.stime=(select min(stime) from #temp where idbo=A4.idbo and sdate=A4.sdate and stime>A3.stime)
left join #temp A5
on A5.idbo=A1.idbo
and A5.sdate=A1.sdate
and A5.stime=(select min(stime) from #temp where idbo=A5.idbo and sdate=A5.sdate and stime>A4.stime)
left join #temp A6
on A6.idbo=A1.idbo
and A6.sdate=A1.sdate
and A6.stime=(select min(stime) from #temp where idbo=A6.idbo and sdate=A6.sdate and stime>A5.stime)
left join #temp A7
on A7.idbo=A1.idbo
and A7.sdate=A1.sdate
and A7.stime=(select min(stime) from #temp where idbo=A7.idbo and sdate=A7.sdate and stime>A6.stime)
left join #temp A8
on A8.idbo=A1.idbo
and A8.sdate=A1.sdate
and A8.stime=(select min(stime) from #temp where idbo=A8.idbo and sdate=A8.sdate and stime>A7.stime)order by A1.Sdate,A1.idbodrop table #temp/*(14 row(s) affected)idbo sdate T1 T2 T3 T4 T5 T6 A7 T8
-------- ---------- -------- -------- -------- -------- -------- -------- -------- --------
000008 2007/09/26 08:00:00 12:30:00 13:30:00 17:00:00 NULL NULL NULL NULL
000011 2007/09/26 07:30:00 11:30:00 11:45:00 16:30:00 16:45:00 21:00:00 NULL NULL
000008 2007/09/27 08:20:07 12:20:00 13:00:00 17:30:00 NULL NULL NULL NULL(3 row(s) affected)
*/
000008 1 2007/09/26 08:00:00
000008 1 2007/09/26 12:30:00
000008 1 2007/09/26 13:30:00
000008 1 2007/09/26 17:00:00
000011 1 2007/09/26 07:30:00
000011 1 2007/09/26 11:30:00
000011 1 2007/09/26 11:45:00
000011 1 2007/09/26 16:30:00
000011 1 2007/09/26 16:45:00
000011 1 2007/09/26 21:00:00
000008 1 2007/09/27 08:20:07
000008 1 2007/09/27 12:20:00
000008 1 2007/09/27 13:00:00
000008 1 2007/09/27 17:30:00
得到查询结果
idno Sdate T1 T2 T3 T4 t5 T6 T7 T8
000008 2007/09/26 08:00:00 12:30:00 13:30:00 17:00:00
000011 2007/09/26 07:30:00 11:30:00 11:45:00 16:30:00 16:45:00 21:00:00
000008 2007/09/27 08:20:07 12:20:00 13:00:00 17:30:00