SQL语句汇总

[shsyzl007]

数据表结构
username type num
aaaa 玉米 1212
aaaa 玉米 212
bbb 小麦 2323
bbb ....
只有两种产品 玉米和小麦，玉米价格1.5，小麦价格1.2 求总价值？

[回首笑浮生]

WITH t0 AS ( SELECT producttype,SUM(productnum) AS productnum,(CASE WHEN producttype='玉米' THEN 1.5 ELSE 1.2 END ) AS price FROM tbl GROUP BY producttype )SELECT SUM(productnum*price) FROM t0

[Hello World,]

select 'a' as name ,100 as number into #temp
union select 'a',200
union select 'b',300
union select 'b',100

select * from #temp

select sum(case name when 'a' then 2 else 5 end * number) as amount from #temp

[江南小鱼]

select sum(case username when 'aaaa' then num else 0 end)*1.5 + sum(case username when 'bbb' then num else 0 end)*1.2 from table

[全栈极简]

with a as(
select type,sum(num) as num from foods group by type),

b as(
select type,(case when type='小麦' then 1.2 when type='玉米' then 1.5 end)*num as price  from a)

select sum(price) as result from b

[sgyiliya]

推荐2楼，这个简单明了： select sum(case username when 'aaaa' then num else 0 end)*1.5 + sum(case username when 'bbb' then num else 0 end)*1.2 from table

[sgyiliya]

引用 5 楼 sgyiliya 的回复:

推荐2楼，这个简单明了： select sum(case username when 'aaaa' then num else 0 end)*1.5 + sum(case username when 'bbb' then num else 0 end)*1.2 from table

说错了，推荐3楼。