表是这样的 销量表 id是编码 price 是单价 quantity 是数量 totalprice是总价
id price quantity totalprice
1 100 1 100
1 100 2 200
2 200 1 200
3 400 1 400
3 400 2 800
4 600 1 600
5 700 2 1400
现在想做一张这样的查询
价格区间 销售金额 销量
0-300
301-600
601以上
请问这样的查询SQL该如何写呢
------解决方案--------------------------------------------------------
SELECT '0-300',SUM(CASE WHEN price<=300 THEN quantity ELSE 0 END),
SUM(CASE WHEN price<=300 THEN totalprice ELSE 0 END) FROM TT
UNION
SELECT '301-600',SUM(CASE WHEN price BETWEEN 301 AND 600 THEN quantity ELSE 0 END),
SUM(CASE WHEN price BETWEEN 301 AND 600 THEN totalprice ELSE 0 END) FROM TT
UNION
SELECT '601以上',SUM(CASE WHEN price>=601 THEN quantity ELSE 0 END),
SUM(CASE WHEN price>=601 THEN totalprice ELSE 0 END) FROM TT
UNION
------解决方案--------------------------------------------------------
- SQL code
select '0-300' as 价格带, sum(totalprice) as 销售金额 from 表 where price<=300union all select '301-600' as 价格带, sum(totalprice) as 销售金额 from 表 where price BETWEEN 301 AND 600union all select '601以上' as 价格带, sum(totalprice) as 销售金额 from 表 where price>=601