主页
基础类
应用实例
新技术前沿

一個複雜sql得編寫，請大家幫忙，謝謝

jackbaby 2008-01-22 02:55:43

BK0708220001 XM01 2007-08-21 08:55:00.000 2060000169
BK0708220001 XM01 2007-08-21 09:10:00.000 2060000169
BK0708220001 XM01 2007-08-21 09:45:00.000 2060000169
BK0708220001 XM01 2007-08-21 11:15:00.000 2060000169
BK0708220001 XM01 2007-08-21 16:05:00.000 2060000169
BK0708220001 XM01 2007-08-21 18:00:00.000 2060000169
BK0708220001 XM01 2007-08-21 18:30:00.000 2060000169
BK0708220001 XM01 2007-08-22 00:40:00.000 2060000169
BK0708220001 XM01 2007-08-22 02:40:00.000 2060000169
BK0708220001 XM01 2007-08-22 09:10:00.000 2060000169
BK0708220001 XM01 2007-08-22 12:40:00.000 2060000169

BK0708230001 XM01 2007-08-23 09:05:00.000 2060000175/6
BK0708230001 XM01 2007-08-23 09:35:00.000 2060000175/6
BK0708230001 XM01 2007-08-23 10:09:00.000 2060000175/6
BK0708230001 XM01 2007-08-24 16:50:00.000 2060000175/6
BK0708230001 XM01 2007-08-24 17:00:00.000 2060000175/6
BK0708230001 XM01 2007-08-24 19:00:00.000 2060000175/6
BK0708230001 XM01 2007-08-24 19:30:00.000 2060000175/6
BK0708230001 XM01 2007-08-25 00:40:00.000 2060000175/6
BK0708230001 XM01 2007-08-25 02:40:00.000 2060000175/6
BK0708230001 XM01 2007-08-25 09:25:00.000 2060000175/6
BK0708230001 XM01 2007-08-25 13:30:00.000 2060000175/6

的數據，我要求

BK0708220001 XM01 2007-08-21 08:55:00.000,2007-08-21 09:10:00.000... 2060000169
BK0708220001 XM01 2007-08-21 08:55:00.000,2007-08-21 09:10:00.000... 2060000175/6

的數據，要怎麽寫呢？？謝謝各位了，謝謝

...全文

9 条回复

切换为时间正序

当前发帖距今超过3年，不再开放新的回复

发表回复

yangjiexi 2008-01-22



  declare @L1 varchar(30)

  declare @L3 varchar(30)

  declare Lcursor Cursor for 

	select L1,L3 from #aa group by L1,L3



  Open Lcursor

  fetch next from Lcursor into @L1,@L3



  while @@fetch_status=0

  begin

    declare @SQL varchar(8000)

	set @SQL=@L1

	select @SQL=@SQL+' '+L2+' ' from #aa

	where L1=@L1 and L3=@L3

	select @SQL=@SQL+@L3    --少了一句

	insert into results select @SQL

    fetch next from Lcursor into @L1,@L3

  end



  close Lcursor    

  deallocate Lcursor

回复赞

jackbaby 2008-01-22

謝謝各位，問題估計解決。。研究dawugui 的思想中。赫赫

回复赞

JiangHongTao 2008-01-22

就是行列转换。参考：

create   table   tb   (bid   int,bname   varchar(10),mpid   int,mpname   varchar(10),con   int,bcon   int) 

insert   into   tb   select   10,'董事会',1,'FB01A',60,5 

insert   into   tb   select   10,'董事会',2,'FB01B',60,0 

insert   into   tb   select   10,'董事会',3,'FB01C',60,0 

insert   into   tb   select   10,'董事会',4,'FB02A',60,8 

insert   into   tb   select   10,'董事会',5,'FB02B',60,0 

insert   into   tb   select   10,'董事会',6,'FB02C',60,2 

insert   into   tb   select   19,'营业',1,'FB01A',60,0 

insert   into   tb   select   19,'营业',2,'FB01B',60,0 

insert   into   tb   select   19,'营业',3,'FB01C',60,0 

insert   into   tb   select   19,'营业',4,'FB02A',60,0 

insert   into   tb   select   19,'营业',5,'FB02B',60,0 

insert   into   tb   select   19,'营业',6,'FB02C',60,0 

declare   @sql   varchar(8000) 

create   table   #tt(mpid   int,mpname   varchar(1000),con   int) 

set   @sql   =   'alter   table   #tt   add     ' 

select     @sql=@sql+bname+'   int,   '   from   tb   group   by   bname 

set   @sql   =   left(@sql,len(@sql)-1) 

exec(@sql) 

set   @sql   =   'insert   #tt   select   mpid,mpname,con' 

select   @sql   =   @sql   +   '   ,   max(case   bname   when   '''   +   bname   +   '''   then   bcon   else   0   end)   ['   +   bname   +   '分配数]' 

from   (select   distinct   bname   from   tb   )   as   m 

set   @sql   =   @sql   +   '   from   tb   group   by   mpid,mpname,con' 

select   @sql 

exec(@sql) 

select   *   from   #tt 

drop   table   #tt

回复赞

yangjiexi 2008-01-22



declare table tb(text1 varchar(100))



insert into tb

select 'BK0708220001 XM01 2007-08-21   08:55:00.000 2060000169 '

union

select 'BK0708220001 XM01 2007-08-21   09:10:00.000 2060000169 '

union

select 'BK0708220001 XM01 2007-08-21   09:45:00.000 2060000169'

union

select 'BK0708220001 XM01 2007-08-21   11:15:00.000 2060000169'

union

select 'BK0708220001 XM01 2007-08-21   16:05:00.000 2060000169'

union

select 'BK0708220001 XM01 2007-08-21   18:00:00.000 2060000169'

union

select 'BK0708220001 XM01 2007-08-21   18:30:00.000 2060000169 '

union

select 'BK0708220001 XM01 2007-08-22   00:40:00.000 2060000169'

union

select 'BK0708220001 XM01 2007-08-22   02:40:00.000 2060000169'

union

select 'BK0708220001 XM01 2007-08-22   09:10:00.000 2060000169'

union

select 'BK0708220001 XM01 2007-08-22   12:40:00.000 2060000169 '

union

select 'BK0708230001 XM01 2007-08-23   09:05:00.000 2060000175/6 '

union

select 'BK0708230001 XM01 2007-08-23   09:35:00.000 2060000175/6'

union

select 'BK0708230001 XM01 2007-08-23   10:09:00.000 2060000175/6'

union

select 'BK0708230001 XM01 2007-08-24   16:50:00.000 2060000175/6'

union

select 'BK0708230001 XM01 2007-08-24   17:00:00.000 2060000175/6 '

union

select 'BK0708230001 XM01 2007-08-24   19:00:00.000 2060000175/6'

union

select 'BK0708230001 XM01 2007-08-24   19:30:00.000 2060000175/6 '

union

select 'BK0708230001 XM01 2007-08-25   00:40:00.000 2060000175/6 '

union

select 'BK0708230001 XM01 2007-08-25   02:40:00.000 2060000175/6 '

union

select 'BK0708230001 XM01 2007-08-25   09:25:00.000 2060000175/6 '

union

select 'BK0708230001 XM01 2007-08-25   13:30:00.000 2060000175/6'



--select * from @tb



select substring(text1,1,17) L1,substring(text1,19,25) L2,

	substring(text1,45,len(text1)-44) L3

into #aa

from tb



create table results(text2 varchar(8000))



  declare @L1 varchar(30)

  declare @L3 varchar(30)

  declare Lcursor Cursor for 

	select L1,L3 from #aa group by L1,L3



  Open Lcursor

  fetch next from Lcursor into @L1,@L3



  while @@fetch_status=0

  begin

    declare @SQL varchar(8000)

	set @SQL=@L1

	select @SQL=@SQL+' '+L2+' ' from #aa

	where L1=@L1 and L3=@L3

	insert into results select @SQL

    fetch next from Lcursor into @L1,@L3

  end



  close Lcursor    

  deallocate Lcursor

回复赞

dawugui 2008-01-22

create table tb(id varchar(20), rq datetime , val varchar(20))

insert into tb values('BK0708220001 XM01', '2007-08-21 08:55:00.000', '2060000169') 

insert into tb values('BK0708220001 XM01', '2007-08-21 09:10:00.000', '2060000169') 

insert into tb values('BK0708220001 XM01', '2007-08-21 09:45:00.000', '2060000169') 

insert into tb values('BK0708220001 XM01', '2007-08-21 11:15:00.000', '2060000169') 

insert into tb values('BK0708220001 XM01', '2007-08-21 16:05:00.000', '2060000169') 

insert into tb values('BK0708220001 XM01', '2007-08-21 18:00:00.000', '2060000169') 

insert into tb values('BK0708220001 XM01', '2007-08-21 18:30:00.000', '2060000169') 

insert into tb values('BK0708220001 XM01', '2007-08-22 00:40:00.000', '2060000169') 

insert into tb values('BK0708220001 XM01', '2007-08-22 02:40:00.000', '2060000169') 

insert into tb values('BK0708220001 XM01', '2007-08-22 09:10:00.000', '2060000169') 

insert into tb values('BK0708220001 XM01', '2007-08-22 12:40:00.000', '2060000169') 

insert into tb values('BK0708230001 XM01', '2007-08-23 09:05:00.000', '2060000175/6') 

insert into tb values('BK0708230001 XM01', '2007-08-23 09:35:00.000', '2060000175/6') 

insert into tb values('BK0708230001 XM01', '2007-08-23 10:09:00.000', '2060000175/6') 

insert into tb values('BK0708230001 XM01', '2007-08-24 16:50:00.000', '2060000175/6') 

insert into tb values('BK0708230001 XM01', '2007-08-24 17:00:00.000', '2060000175/6') 

insert into tb values('BK0708230001 XM01', '2007-08-24 19:00:00.000', '2060000175/6') 

insert into tb values('BK0708230001 XM01', '2007-08-24 19:30:00.000', '2060000175/6') 

insert into tb values('BK0708230001 XM01', '2007-08-25 00:40:00.000', '2060000175/6') 

insert into tb values('BK0708230001 XM01', '2007-08-25 02:40:00.000', '2060000175/6') 

insert into tb values('BK0708230001 XM01', '2007-08-25 09:25:00.000', '2060000175/6') 

insert into tb values('BK0708230001 XM01', '2007-08-25 13:30:00.000', '2060000175/6') 

go



declare @sql varchar(8000)

set @sql = 'select id '

select @sql = @sql + ' , max(case px when ''' + cast(px as varchar) + ''' then rq else null end) [rq' + cast(px as varchar) + ']'

from (select distinct px from (select *,px=(select count(1) from tb where id = t.id and convert(varchar(10),rq,120) = convert(varchar(10),t.rq,120) and rq < t.rq) + 1 from tb t) m) as a

set @sql = @sql + ' , val from (select *,px=(select count(1) from tb where id = t.id and convert(varchar(10),rq,120) = convert(varchar(10),t.rq,120) and rq < t.rq) + 1 from tb t) m group by id , val'

exec(@sql) 



drop table tb



/*

id                   rq1                                                    rq2                                                    rq3                                                    rq4                                                    rq5                                                    rq6                                                    rq7                                                    val                  

-------------------- ------------------------------------------------------ ------------------------------------------------------ ------------------------------------------------------ ------------------------------------------------------ ------------------------------------------------------ ------------------------------------------------------ ------------------------------------------------------ -------------------- 

BK0708220001 XM01    2007-08-22 00:40:00.000                                2007-08-22 02:40:00.000                                2007-08-22 09:10:00.000                                2007-08-22 12:40:00.000                                2007-08-21 16:05:00.000                                2007-08-21 18:00:00.000                                2007-08-21 18:30:00.000                                2060000169

BK0708230001 XM01    2007-08-25 00:40:00.000                                2007-08-25 02:40:00.000                                2007-08-25 09:25:00.000                                2007-08-25 13:30:00.000                                NULL                                                   NULL                                                   NULL                                                   2060000175/6

*/