问用SQL语句怎样写分组合计?
问用SQL语句怎样写分组合计? (用GROUP BY)
字段：mc bb je
a1 HKD 100
a1 HKD 200
a1 USD 150
a1 USD 250
a2 HKD 200
a2 USD 100
a3 HKD 300
a3 HKD 400
先对a分组,再分别合计b=h和b=u的数放在sumhk,sumus字段中
结果：客户名称 港币 美金
a1 300 400
a2 200 100
a3 700 0
我的写法:
Select sum1.mc as 客户名称,sum(sum1.hk) as 港币, sum(sum1.us) as 美金
from
(select mc,sum(je) as hk, 0 as us from data
where bb = 'HKD'
group by mc
UNION all
select mc,0 as hk, sum(je) as us from data
where bb = 'USD'
group by mc) as sum1
group by mc
提示:FORM错误
select 客户名称,
sum(case when bb='HKD' then je else 0 end) as 港币,
sum(case when bb='USD' then je else 0 end) as 美金
from data group by mc
select mc,sum(case left(bb,1) when 'h' then je else 0 end) as 港币 ,sum(case left(bb,1) when 'u' then je else 0 end) as 美金
from tab group by mc
select 客户名称,
sum(case when bb='HKD' then je else 0 end) as 港币,
sum(case when bb='USD' then je else 0 end) as 美金
from data group by mc
这个写法有错的!!
请写一个对的!
少个MC
select mc 客户名称,
sum(case when bb='HKD' then je else 0 end) as 港币,
sum(case when bb='USD' then je else 0 end) as 美金
from date group by mc
select mc as 客户名称,
sum(case when bb='HKD' then je else 0 end) as 港币,
sum(case when bb='USD' then je else 0 end) as 美金
from date group by mc
这样写也不能!!
select mc as 客户名称,
sum(case when bb='HKD' then je else 0 end) as 港币,
sum(case when bb='USD' then je else 0 end) as 美金
from date group by mc
这样写也不能!!
select mc as 客户名称,
sum(case when bb='HKD' then je else 0 end) as 港币,
sum(case when bb='USD' then je else 0 end) as 美金
from date group by mc
没问题阿!
