SQL有意思的面试题

1、中软国际 SQL行转列

 变成  

 

--数据准备
create table t_test(
    year int,
    month int,
    sale int,
    primary key (year, month)
);

insert into t_test values (1991, 1, 110);
insert into t_test values (1991, 2, 120);
insert into t_test values (1991, 3, 130);
insert into t_test values (1991, 4, 140);
insert into t_test values (1992, 1, 210);
insert into t_test values (1992, 2, 220);
insert into t_test values (1992, 3, 230);
insert into t_test values (1992, 4, 240);

select year as '年份',
    max(case month when 1 then sale else 0 end) as '一月',
    max(case month when 2 then sale else 0 end) as '二月',
    max(case month when 3 then sale else 0 end) as '三月',
    max(case month when 4 then sale else 0 end) as '四月',
    max(case month when 5 then sale else 0 end) as '五月'
from t_test group by year;

 

二、东方通达 复杂查询

 

--标准版
SELECT  a.class,  AVG(score),  COUNT(username)  FROM
        (SELECT  class,  avg(score)  FROM  table1  LEFT JOIN  table2 
                ON  table1.username = table2.username  GROUP BY  class)  AS  a
　　　　  LEFT  JOIN
    　　 (SELECT  class,  COUNT(username)  FROM  table1  LEFT JOIN  table2 
                ON  table1.username = table2.username  WHERE  score < 60  GROUP BY  class)  AS c
ON  a.class = c.class;

--变态版
select class, avg(score), sum(if(score < 60, 1, 0))
 from table1 left join table2  ON  table1.username = table2.username  GROUP BY  class