求一条SQL语句,怎么把表里边的数据按一定的条件筛选出来求和(100分)

D

daoba_wolf

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2006-12-05

• #1

例如我有一个表:
编码 数量
1000001 234534
1000002 343
1000003 2
2000003 34
2000004 9789
2000005 5096
3000006 54645
3000007 4564
3000008 564
4000009 435
4000010 5656
4000011 43
想要得到的结果是某一编码段的和,最好是如:
编码 数量
1000000 234879
2000000 14919
3000000 59773
4000000 6134
哪位大侠能帮助小弟,实在是感恩不尽.

 

S

suger

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2006-12-05

• #2

select NewNum, Sum(数量)
from (
select 编码, 数量, Left(编码, 5) + '0' as NewNum from Table
) c
group by NewNum, 数量

 

Y

Yhhe

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2006-12-05

• #3

select 编码=substring(编码,1,1)+'000000',数量=sum(数量) from 表 group by substring(编码,1,1) order by 1

 

D

daoba_wolf

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2006-12-05

• #4

不行啊,两位大哥,测试不出来想要的结果.

 

S

sunnyfairy

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2006-12-05

• #5

Select Left(编码, 5) + '0' 编码,
(Select Sum(数量)
From Table b
Where Left(a.编码, 5) ＝ Left(b.编码, 5))
From Table a

 

李

李翔鹏

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2006-12-06

• #6

select NewNum, Sum(数量)
from (
select 编码, 数量, Left(编码, 5) + '0' as NewNum from Table
) c
group by NewNum, 数量

 

C

ChrisMao

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2006-12-06

• #7

TO daoba_wolf：
如果你的编码是int类型的，可以试一下这条SQL语句
Select (编码 / 100000) * 100000 as 编码, Sum(数量)
From Table
Group by 编码 / 100000

如果编码是字符型的，可以试一下这条
Select (Convert(int, 编码) / 100000) * 100000 as 编码, Sum(数量)
From Table
Group by Convert(int, 编码) / 100000

 

W

wangbinsoft

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2006-12-06

• #8

SELECT left(编码,1) AS 分类编码 , sum(编码) as 总数量 FROM 表 group by left(编码,1)
我是默认编码字段是字符型的，如果是整数型的，见下：
SELECT 编码mod10000*10000 AS 分类编码 , sum(编码) as 总数量 FROM 表 group by 编码mod10000*10000

 

G

gaobangsheng

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2006-12-06

• #9

select 編碼+'0000000',sum(數量) as 數量
from (select left(編碼,1) as 編碼,數量
from TableName )
group by 編碼

 

L

lzh1983

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2006-12-06

• #10

select substring(编码,1,1)+'000000' as 编码,sum(数量) as 数量 from table
group by substring(编码,1,1)
order by substring(编码,1,1)

 

M

Milpas

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2006-12-06

• #11

呵呵 好象上面给出了ACCESS, SQL SERVER, ORACLE的各种写法

 

王

王亮

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2006-12-06

• #12

假设表名为ttest，编码字段为dm,数量字段为sl
假设取编码前两位相同的进行分组，则：
select DISTINCT left(dm, 2) + '000000' as ddm,
(select sum(sl) from ttest where left(dm, 2) = left(b.dm, 2))
from ttest b

（注：此语句SQLServer2000下测试通过。）

 

D

daoba_wolf

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2006-12-09

• #13

谢谢各位大侠的帮助,gaobangsheng大哥得出我想要的结果.