SQL面试题

1.第一题，有以下数据

year	season	money
2008	s1	100
2008	s2	2
2009	s1	87
2010	s1	70
2010	s2	238
2010	s3	33
2011	s4	435

需求1：写一个SQL统计money字段求和小于300得年份

select year from table group by year having sum(money)<300;

需求2：写一个SQL实现一下查询结果(不限制SQL语法):

year	s1	s2	s3	s4
2008	100	2	0	0
2009	87	0	0	0
2010	70	238	33	0
2011	0	0	0	435

SELECT
 year,
 ifnull(SUM(
  CASE
  WHEN season = 's1' THEN
   money
  END
 ),0) AS s1,
 ifnull(sum(
  CASE
  WHEN season = 's2' THEN
   money
  END
 ),0) AS s2,
 ifnull(sum(
  CASE
  WHEN season = 's3' THEN
   money
  END
 ),0) AS s3,
 ifnull(sum(
  CASE
  WHEN season = 's4' THEN
   money
  END
 ),0) AS s4
FROM
 my_test
GROUP BY
 year

第二题

year	dept
2008	dev
2008	sale
2009	dev
2010	hr
2010	man
2010	market
2011	service

需求：写一个SQL实现以下查询结果(不限制SQL语法)：

year	dept
2008	dev,sale
2009	dev
2010	hr,man,market
2011	service

SELECT
 year,
 GROUP_CONCAT(dept) AS dept
FROM
 table
GROUP BY
 year;