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

2008-01-22 02:55:43 136 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

一个跨库复杂查询的SQL优化的案例导读为了帮客户出一个报表，需要跨三个库进行一个复杂的查询，用到了 in，union all，left join等关键字，其中几个字段还需要进行SUM，Count等计算。未优化前查询耗时368秒。待优化场景先看一下客户程序员写的这个复杂的SQL语句吧，看了都头大。虽说辉哥见多识广，还是被这个SQL吓到了。 select distinct a.cn,e.chinese_nam 阅读详情

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

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

...全文

一个注解解决ShardingJdbc不支持复杂SQL 公司最近做分库分表业务，接入了 Sharding JDBC，接入完成后，回归测试时发现好几个 SQL 执行报错，关键这几个表都还不是分片表。报错如下：这下糟了嘛。熟悉 Sharding JDBC 的同学应该知道，有很多 SQL 它是不支持的。官方截图如下：如果要去修改这些复杂 SQL 的话，可能要花费很多时间。那怎么办呢？只能从 Sharding JDBC 这里找突破口了，两天的研究，出来了下面这个只需要加一个注解轻松解决 Sharding Jdbc 不支持复杂 SQL 的方案。阅读详情

jackbaby

MS-SQL Server 34,876 成员 254,635 内容

+ 关注 + 加入社区

切换为时间正序

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

打赏
举报

什么意思?没看明白.

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

相关推荐