根据teacher_no统计星期的上课数

发布时间：2022-03-30 12:27 所属栏目：115 来源：互联网

导读：根据teacher_no统计星期的上课数： -- 解法一 SELECT t1.teacher_no, (CASE WEEK WHEN 1 THEN keshu ELSE 0 END ) 礼拜一, (CASE WEEK WHEN 2 THEN keshu ELSE 0 END ) 礼拜二, (CASE WEEK WHEN 3 THEN keshu ELSE 0 END ) 礼拜三 FROM ( SELECT teacher_no,

  根据teacher_no统计星期的上课数：

-- 解法一
SELECT
t1.teacher_no,
(CASE WEEK WHEN '1' THEN keshu ELSE 0 END ) 礼拜一,
  (CASE WEEK WHEN '2' THEN keshu ELSE 0 END ) 礼拜二,
  (CASE WEEK WHEN '3' THEN keshu ELSE 0 END ) 礼拜三
FROM
(
    SELECT
      teacher_no,
      WEEK,
      COUNT(1) AS keshu
      FROM teach
  GROUP BY teacher_no,WEEK
)t1
  GROUP BY t1.teacher_no;

-- 解法二就是count()函数作用
SELECT
  t1.teacher_no,
  (CASE WEEK WHEN '1' THEN keshu ELSE 0 END ) 礼拜一,
  (CASE WEEK WHEN '2' THEN keshu ELSE 0 END ) 礼拜二,
  (CASE WEEK WHEN '3' THEN keshu ELSE 0 END ) 礼拜三
FROM
(
  SELECT
  t.teacher_no,
  t.WEEK,
  COUNT(t.keshu) AS keshu
  FROM
      (
      SELECT
      teacher_no,
      WEEK,
      '1' keshu
      FROM teach )t
  GROUP BY teacher_no,WEEK
    )t1
GROUP BY t1.teacher_no

（编辑：ASP站长网）