SQL查询面试题

1、统计每年每月的信息
year month amount
1991   1        1.1
1991   2        1.2
1991   3        1.3
1991   4        1.4
1992   1        2.1
1992   2        2.2
1992   3        2.3
1992   4        2.4
查成这样一个结果
year m1 m2 m3 m4
1991 1.1 1.2 1.3 1.4
1992 2.1 2.2 2.3 2.4

准备sql语句：
drop table if exists sales;
create table sales(id int auto_increment primary key,year varchar(10), month varchar(10), amount float(2,1));
insert into sales values
(null,’1991′,’1′,1.1),
(null,’1991′,’2′,1.2),
(null,’1991′,’3′,1.3),
(null,’1991′,’4′,1.4),
(null,’1992′,’1′,2.1),
(null,’1992′,’2′,2.2),
(null,’1992′,’3′,2.3),
(null,’1992′,’4′,2.4);

答案：
select sales.year ,
(select t.amount from sales t where t.month=’1′ and t.year= sales.year) ’1′,
(select t.amount from sales t where t.month=’1′ and t.year= sales.year) ’2′,
(select t.amount from sales t where t.month=’1′ and t.year= sales.year) ’3′,
(select t.amount from sales t where t.month=’1′ and t.year= sales.year) as ’4′
from sales group by year;

2、分组统计胜负

表内容：
2005-05-09 胜
2005-05-09 胜
2005-05-09 负
2005-05-09 负
2005-05-10 胜
2005-05-10 负
2005-05-10 负

如果要生成下列结果, 该如何写sql语句?

            胜 负
2005-05-09 2 2
2005-05-10 1 2
------------------------------------------
create table #tmp(rq varchar(10),shengfu nchar(1))

insert into #tmp values('2005-05-09','胜')
insert into #tmp values('2005-05-09','胜')
insert into #tmp values('2005-05-09','负')
insert into #tmp values('2005-05-09','负')
insert into #tmp values('2005-05-10','胜')
insert into #tmp values('2005-05-10','负')
insert into #tmp values('2005-05-10','负')

1)select rq, sum(case when shengfu='胜' then 1 else 0 end)'胜',sum(case when shengfu='负' then 1 else 0 end)'负' from #tmp group by rq

2) select N.rq,N.勝,M.負 from (
select rq,勝=count(*) from #tmp where shengfu='胜'group by rq)N inner join
(select rq,負=count(*) from #tmp where shengfu='负'group by rq)M on N.rq=M.rq

3)select a.col001,a.a1 胜,b.b1 负 from
(select col001,count(col001) a1 from temp1 where col002='胜' group by col001) a,
(select col001,count(col001) b1 from temp1 where col002='负' group by col001) b
where a.col001=b.col001