sql if 按条件求汇总 else

Question

规格 数量（个） 部门
-----------------------
a 11 001
a 3 001
a 1 003
b 12 001
c 13 002
c 15 001
d 20 003
d 25 001
-----------------------
注：a规格1元/个，b规格2元/个，c规格3元/个，d规格4元/个
怎样用sql2000查询出以下结果：
按部门汇总出a、b、c、d,四个规格的总金额？
例：001部门XX元
002部门XX元

Answer 1

SELECT 部门,SUM(金额) AS 总金额 FROM
(
SELECT 规格,sum(数量) as 数量,部门,
CASE WHEN 规格='a' THEN SUM(数量*1)
WHEN 规格='b' THEN SUM(数量*2)
WHEN 规格='c' THEN SUM(数量*3)
WHEN 规格='d' THEN SUM(数量*4)
END AS 金额
FROM 表 GROUP BY 部门,规格
) A GROUP BY 部门

Answer 2

select 部门,sum(数量) * (decode (规格,'a',1,'b',2,'c',3,4) )
from table
group by 部门

Answer 3

假定该表为表table1，对应字段为guige，shuliang，bumen
sql语句可以写为：
select bumen,sum(bufenjinge) as zongjinge
from
(
select a1.shuliang*a2.jage as bufenjinge,a1.bumen from
table1 as a1 left outer join
(
select 1 as jiage,'a' as guige
union
select 2 as jiage,'b' as guige
union
select 3 as jiage,'c' as guige
union
select 4 as jiage,'d' as guige
) as a2
on a1.guige=a2.guige
) as c
group by bumen

--------------------------------------------------
你将表名字段名作适当修改，上面语句应该能满足你的要求。比较好的做法是建一个新表，将规格和价格字段存入该表，再join，最后汇总。