这个sql怎么写,要效率高点的(比较难)

nicq2008 2004-11-21 04:00:07
有17个表(表结构都相同) (他们的记录数都永远是900,但每个表的id值会不断递增,且无序)
字段分别是 id,value
现在每个表的数据(都一样的)如下:
表1: 表2: ... ... 表17:
ID VALUE ID VALUE ID VALUE
134 1.01 433 1.02 544 1.06
135 1.03 444 1.03 545 1.03
136 1.00 445 1.01 546 1.05
现在我想写一个SQL,得到如下:
ID1 ID2 VALUE1 VALUE2 ... ... ID17 VALUE17
134 433 1.01 1.02 544 1.06
135 444 1.03 1.03 545 1.03
136 445 1.00 1.01 546 1.05

怎么写啊?
...全文
118 5 打赏 收藏 转发到动态 举报
写回复
用AI写文章
5 条回复
切换为时间正序
请发表友善的回复…
发表回复
lsxaa 2004-11-21
  • 打赏
  • 举报
回复
前提是 每个表的ID之间是递增的
select id1,id2,id3,id4,....id17,value1,value2,...value17
from t1,t2,...,t17
where t1.id-(select min(id) from t1)=t2.id-(select min(id) from t2)
and t1.id-(select min(id) from t1)=t3.id-(select min(id) from t3)
and ...
and t1.id-(select min(id) from t1)=t17.id-(select min(id) from t17)

Yang_ 2004-11-21
  • 打赏
  • 举报
回复
以上最好加
order by x1.id

如果只有900行,以上语句效率也许可以接受,如果数据量大,效率可能成为大问题,那最好是用临时表了:

select id as id1,value as value1,
IDENTITY(int,1,1) as id
into #t1
from tab1
order by id

select id as id2,value as value2,
IDENTITY(int,1,1) as id
into #t2
from tab2
order by id

...

select id as id17,value as value17,
IDENTITY(int,1,1) as id
into #t17
from tab17
order by id

select id1,id2,id3,id4,....id17,value1,value2,...value17
from #t1 x1,#t2 x2,...,#t17 x17
where x1.id=x2.id
and x1.id=x3.id
and ...
and x1.id=x17.id
order by x1.id

drop table #t1,#t2,...#t17



lsxaa 2004-11-21
  • 打赏
  • 举报
回复
select id1,id2,id3,id4,....id17,value1,value2,...value17
from t1,t2,...,t17
where t1.id-(select min(id) from t1)=t2.ids-(select min(id) from t2)
and t1.id-(select min(id) from t1)=t3.ids-(select min(id) from t3)
and ...
and t1.id-(select min(id) from t1)=t17.ids-(select min(id) from t17)

Yang_ 2004-11-21
  • 打赏
  • 举报
回复
注意以下语句效率比较低:

select id1,id2,id3,id4,....id17,value1,value2,...value17
from (
select id as id1,value as value1,
(select count(*) from tab1 where id<=a1.id) as id
from tab1 a1
) as x1,(
select id as id2,value as value2,
(select count(*) from tab2 where id<=a2.id) as id
from tab2 a2
) as x2,
...
(
select id as id17,value as value17,
(select count(*) from tab17 where id<=a17.id) as id
from tab17 a17
) as x17
where x1.id=x2.id
and x1.id=x3.id
and ...
and x1.id=x17.id

ORARichard 2004-11-21
  • 打赏
  • 举报
回复
为每个表都增加一个表示行号的字段,这样才能高效的完成你的要求。(各表间通过行号关联)

34,571

社区成员

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

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