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

的數據，要怎麽寫呢？？謝謝各位了，謝謝
...全文
105 9 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
9 条回复
切换为时间正序
请发表友善的回复…
发表回复
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
  • 打赏
  • 举报
 回复
什么意思?没看明白.
如何使用 ChatGPT 编写 SQL JOIN 查询
是一个在Windows平台被广泛使用的MySQL客户端，它能够使MySQL开发人员和管理人员在一个方便的环境中与他人一起完成创建和执行查询，开发和调试MySQL程序，自动化管理MySQL数据库对象等工作。在可预见的未来，ChatGPT...
SQL的弱点（1）：复杂SQL不易理解，以及软件工程如何来帮忙
这里就不再列举SQL的优点了（很多），而只谈谈SQL使用中的一些问题，这里是系列文章的开篇：复杂SQL不易理解。 2. 讲故事 先讲个故事来示例，注： 示例中的表和场景都是经过简化的，实际中可能复杂非常多 示例的...
用AI赋能SQL开发，让数据查询更高效
标题：用AI赋能SQL开发，让数据查询更高效 在现代软件开发中，SQL语句...然而，对于许多开发者来说，编写和优化SQL语句仍然是一个耗时且容易出错的过程。尤其是在面对大型数据库或复杂查询需求时，传统的手动编写方...
纯文本分析方法，从复杂的sql查询语句中提取所有来源表名
前段时间开发了一个小工具用来检测一些sql脚本，其中有一个步骤需要将查询语句的各个来源表都提取出来。本来借助数据库是可以实现的，但该方法有局限性。所以我一直在想，能不能通过纯文本分析的方法来解决这个问题...
太爽了！！用这款AI工具我可以横着走写SQL
太爽了！！用这款AI工具我可以横着走写SQL
MS-SQL Server

34,837

社区成员

254,632

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章