数据库分组求和语句

 
表包含三列，[id]   ,[countNo],[type]，id 表示仓库编号，countno表示货物数量，[type]表示货物类型

 

现在要求统计每个仓库各个货物的数量，包含四列仓库编号，货物1的数量，货物2的数量，货物3的数量..货物N的数量。

 

1 第一种方法，使用inner join和as给表取别名，语句如下：

 

[sql]

select a.id ,sum(a.countno) countno1,sum(b.countno) countno2 ,sum(c.countno)  countno3  

from [Table_1] as a   

inner join [Table_1] as b on a.id=b.id  

inner join [Table_1] as c on a.id=c.id  

where a.type=1 and b.type=2.and c.type=3   

group by a.id  

 或者是iner join的另外一种写法

 

[sql]

select a.id ,sum(a.countno),sum(b.countno),sum(c.countno)   

from [Table_1] as a ,  

 [Table_1] as b   

,[Table_1] as c  

where a.type=1 and b.type=2 and c.type=3 and a.id=b.id and a.id=c.id  

group by a.id,a.type  

 

2使用case when方法，语句如下

 

[sql]

select a.id ,  

sum(case  when type=1 then countno else 0 end),  

sum(case  when type=2 then countno else 0 end),  

sum(case  when type=3 then countno else 0 end)  

from [Table_1] as a group by a.id  

 

这两种方式其实都是假定货物种类是确定的，如何货物种类不确定，或者经常变动，如何操作？就需要借助于游标或者临时表之类的复杂语句了。

 

 

 

摘自 xuexiaodong2009的专栏