--数据准备
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