create table a
(
country varchar(20),
state varchar(10),
cout int
)
insert into a values('中国','A',100);
insert into a values('中国','B',400);
insert into a values('中国','C',300);
insert into a values('中国','D',600);
insert into a values('美国','A',545);
insert into a values('美国','B',130);
insert into a values('美国','C',120);
insert into a values('美国','D',150);
select * from a;
--
-- 查询的结果象这样：
-- 国家 A B C D
-- 中国 100 400 300 600
-- 美国 545 130 120 150
select 国家=country,
'A' =
sum(
case
when state = 'A' then cout
else 0
end
),
'B' =
sum(
case
when state = 'B' then cout
else 0
end
),
'C' =
sum(
case
when state = 'C' then cout
else 0
end
),
'D' =
sum(
case
when state = 'D' then cout
else 0
end
)
from a
group by country
order by 1 desc