积木首页 - 学院 - 软件测试 - 技术百科 - 问答 - 黄页 - 手册 - 站长工具 - 社区
登录 | 注册
积木技术社区 » SqlServer » 一个纠结的SQL语句
打印 楼主:hthymxy 发表于 2011-01-05 15:06 [回复20次/有效19个,浏览222次]

一个纠结的SQL语句

是这样的,我的表的字段
号码 Cathectic
0001 50
……
9999 20
每一个号码都有12种规格AA** A**A AAA* 等
因为数据量还是比较大,所以想要在一个SQL语句里实现查询这12种规格的数值的SUM值
是这样写的(比如查询2312这个数的12种规格)
SELECT
SUM(table1.Cathectic) as 规格1,
SUM(table2.Cathectic) as 规格2,
SUM(table3.Cathectic) as 规格3,
SUM(table4.Cathectic) as 规格4,
SUM(table5.Cathectic) as 规格5,
SUM(table6.Cathectic) as 规格6,
SUM(table7.Cathectic) as 规格7,
SUM(table8.Cathectic) as 规格8,
SUM(table9.Cathectic) as 规格9,
SUM(table10.Cathectic) as 规格10,
SUM(table11.Cathectic) as 规格11
FROM
[numtest] as table1,
[numtest] as table2,
[numtest] as table3,
[numtest] as table4,
[numtest] as table5,
[numtest] as table6,
[numtest] as table7,
[numtest] as table8,
[numtest] as table9,
[numtest] as table10,
[numtest] as table11
WHERE
table1.Number like '23%' AND
table2.Number like '%12' AND
table3.Number like '2%2' AND
table4.Number like '%31%' AND
table5.Number like '%3%2' AND
table6.Number like '2%1%' AND
table7.Number like '231%' AND
table8.Number like '%312' AND
table9.Number like '23%2' AND
table10.Number like '2%12' AND
table11.Number like '2312'
但是这样效率太低了,,555555555555555
我等得都快跳楼了 ,希望各位高手能给出效率较高的查询
在线等……
2楼 发表于 2011-01-05 15:28
SQL code
declare @table table (Number int,Cathectic int)
declare @i int
set @i=1
while @i
3楼 发表于 2011-01-05 15:54
咋不对了?
4楼 发表于 2011-01-05 16:29
TO:(晴天)
这样查询出来的数据好像有点不对啊
5楼 发表于 2011-01-05 16:39
一个一个执行的话倒是很快 1~2秒
但是这样查询一个数,就要查询11次
10000条数据 我的天哪
6楼 发表于 2011-01-05 17:04
结贴!
7楼 发表于 2011-01-05 17:08
SQL code
CREATE table #t
(Number varchar(100) not null ,
Cathectic1 float null,
Cathectic2 float null,
...
Cathectic12 float null,
primary key(Number)
)
--将满足查询条件的 Number 先插入
insert into #t
select Number from [Number表] where XXXXX
--接下来是 12个update
update #t set Cathectic1 =t2.Cathectic
from #t t1 inner join
(select Number ,sum(Cathectic) Cathectic table1 group by Number ) t2 on t1.Number = t2.Number
--最后
select * from #t
8楼 发表于 2011-01-05 17:34
用全局通配符不对,有的必须用局部通配符:
SQL code
create table numtest(number nvarchar(10),cathectic int)
insert into numtest select '1231',28 union all
select '1321',22 union all
select '3213',32 union all
select '2131',55 union all
select '1312',35 union all
select '2312',44 union all
select '2132',34
go
select * from(
select sum(cathectic)as 规格1 from numtest where number like '23%'
)a,(
select sum(cathectic)as 规格2 from numtest where number like '%12'
)b,(
select sum(cathectic)as 规格3 from numtest where number like '2%2'
)c,(
select sum(cathectic)as 规格4 from numtest where number like '_31_'
)d,(
select sum(cathectic)as 规格5 from numtest where number like '_3_2'
)e,(
select sum(cathectic)as 规格6 from numtest where number like '2_1_'
)f,(
select sum(cathectic)as 规格7 from numtest where number like '231%'
)g,(
select sum(cathectic)as 规格8 from numtest where number like '%312'
)h,(
select sum(cathectic)as 规格9 from numtest where number like '23%2'
)i,(
select sum(cathectic)as 规格10 from numtest where number like '2%12'
)j,(
select sum(cathectic)as 规格11 from numtest where number ='2312'
)k
go
drop table numtest
/*
规格1 规格2 规格3 规格4 规格5 规格6 规格7 规格8 规格9 规格10 规格11
----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
44 79 78 79 79 44 44 79 44 44 44
(1 行受影响)
*/
9楼 发表于 2011-01-05 17:46
哈哈 问题解决
谢谢(晴天) SQL code
select * from
(select sum(cathectic)as 规格1 from numtest where Number like '23%')a,
(select sum(cathectic)as 规格2 from numtest where Number like '%12')b,
(select sum(cathectic)as 规格3 from numtest where Number like '2%2')c,
(select sum(cathectic)as 规格4 from numtest where Number like '%31%')d,
(select sum(cathectic)as 规格5 from numtest where Number like '%3%2')e,
(select sum(cathectic)as 规格6 from numtest where Number like '2%1%')f,
(select sum(cathectic)as 规格7 from numtest where Number like '231%')g,
(select sum(cathectic)as 规格8 from numtest where Number like '%312')h,
(select sum(cathectic)as 规格9 from numtest where Number like '23%2')i,
(select sum(cathectic)as 规格10 from numtest where Number like '2%12')j,
(select sum(cathectic)as 规格11 from numtest where Number ='2312')k
10楼 发表于 2011-01-05 18:21
拆开来一个个执行速度如何?
11楼 发表于 2011-01-05 18:54
对 12个表 但实际是一张表
12楼 发表于 2011-01-05 19:12
最好的办法是:
SQL code
alter table numtest add c1 varchar(4),c2 varchar(4),c3 varchar(4),c4 varchar(4)
CREATE NONCLUSTERED INDEX IX_c1 ON dbo.numtest(c1)
CREATE NONCLUSTERED INDEX IX_c2 ON dbo.numtest(c2)
CREATE NONCLUSTERED INDEX IX_c3 ON dbo.numtest(c3)
CREATE NONCLUSTERED INDEX IX_c4 ON dbo.numtest(c4)
update numtest set c1=substring(number,1,1),c2=substring(number,2,1),c3=substring(number,3,1),c4=substring(number,4,1)
然后你想咋查就咋查了.
13楼 发表于 2011-01-05 19:25
大地在等待你.
14楼 发表于 2011-01-05 19:44
如果是表结构不合理,建议重构。
15楼 发表于 2011-01-05 20:20
我想可以试试
16楼 发表于 2011-01-05 20:37
太纠结了 10000条 我查一个就几分钟…………
17楼 发表于 2011-01-05 21:05
。。。。。。。。受不了了
18楼 发表于 2011-01-05 21:24
楼上你错了,人家是 12个表关联
19楼 发表于 2011-01-05 21:45
如果一个表查询是 1-2秒,那么 按 上面的办法 应该20秒左右应该出来了
20楼 发表于 2015-07-01 11:50
还是不错的哦,谢谢楼主的贡献了 即可均已http://www.2ppt.cn
标题:
内容:

PHP: PHP技巧 - PHP基础 - PHP实例 - PHP错误代码 - 正则表达式 - 热门脚本语言: DOS命令 - vbs - CSS/HTML - python - perl - ruby - shell编程 - Powershell

JS: node.js - JS基础 - extjs - jQuery - JavaScript技巧

网址导航成语大全积木 TinyMCE 服务邮箱: Gimoohr(#)gmail.com
Copyright © 2008 Gimoo.Net  京ICP备05050695号