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

一個複雜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

的數據,要怎麽寫呢??謝謝各位了,謝謝
...全文
74 点赞 收藏 9
写回复
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
*/
回复
sdsxlj 2008-01-22
楼主解释一下
回复
conan304 2008-01-22
没看懂,什么意思。
回复
bqb 2008-01-22
我也看不明白,楼主给得结果集是不是有误啊,

是不是根据前两列分组,后面的2007-08-21 08:55:00.000,2007-08-21 09:10:00.000...
用case when 语句就可以实现了!
回复
sdsxlj 2008-01-22
什么意思?没看明白.
回复
发动态
发帖子
MS-SQL Server
创建于2007-09-28

3.2w+

社区成员

MS-SQL Server相关内容讨论专区
申请成为版主
社区公告
暂无公告