(一)、根据teacher_no统计星期的上课数 - MySQL数据库

1. — 解法一
   
2. SELECT
3.   t1.teacher_no,
4.   (CASE WEEK WHEN ’1’ THEN keshu ELSE 0 END ) 礼拜一,
5.   (CASE WEEK WHEN ’2’ THEN keshu ELSE 0 END ) 礼拜二,
6.   (CASE WEEK WHEN ’3’ THEN keshu ELSE 0 END ) 礼拜三
7. FROM
8. (
9.     SELECT
10.       teacher_no,
11.       WEEK,
12.       COUNT(1) AS keshu
13.       FROM teach
14.   GROUP BY teacher_no,WEEK
15.  )t1
16.   GROUP BY t1.teacher_no;
17. 
    
19. — 解法二 就是count()函数作用
20. SELECT
21.   t1.teacher_no,
22.   (CASE WEEK WHEN ’1’ THEN keshu ELSE 0 END ) 礼拜一,
23.   (CASE WEEK WHEN ’2’ THEN keshu ELSE 0 END ) 礼拜二,
24.   (CASE WEEK WHEN ’3’ THEN keshu ELSE 0 END ) 礼拜三
25. FROM
26. (
27.   SELECT
28.   t.teacher_no,
29.   t.WEEK,
30.   COUNT(t.keshu) AS keshu
31.   FROM
32.       (
33.       SELECT
34.       teacher_no,
35.       WEEK,
36.       ’1’ keshu
37.       FROM teach )t
38.   GROUP BY teacher_no,WEEK
39.     )t1
40. GROUP BY t1.teacher_no
41. ;