sql - 列名称与列标题和计数/基于其他表中的列标签的枢轴

Question

我有两个表如下:

表标记列表:

student_id  class_id    subject_1   subject_2   subject_3   subject_4   subject_5
----------- ----------- ----------- ----------- ----------- ----------- ----------
1           9           78          87                                  95
2           9           67          95                                    87
3           9           85          84                                    85
4           10          70                      65          78    
5           10          75                      80          81    
6           10          80                      75          82    

表主题名称

column_name     subject_name
--------------- -------------
subject_1       English
subject_2       Chemistry
subject_3       Economics
subject_4       Accounts
subject_5       Biology

现在，我需要为 class_id = 9 生成这样的报告

column_name     subject_name  no_of_students
--------------- ------------- --------------
subject_1       English       3
subject_2       Chemistry     3
subject_3       Economics     0
subject_4       Accounts      0
subject_5       Biology       3

简而言之，我必须生成一个报告，其中包含 column_names、subject_name 和 class_id = 9(或 10，无论如何)为该主题出现的学生人数。

我所能做的就是

1.

    SELECT sn.column_name, sn.subject_name FROM subject_names sn;

和

2.

    SELECT ml.class_id,
           count(ml.subject_1) AS s1,
           count(ml.subject_2) AS s1,
           count(ml.subject_3) AS s1,
           count(ml.subject_4) AS s1,
           count(ml.subject_5) AS s1,
      FROM marklist ml
     WHERE ml.class_id = 9;

我不明白如何继续将查询 2 的结果转换为查询 1。我可能走错了方向，但我不知道。

Answer 1

您可以 unpivot您的标记列表表和外部将它与 subject_names 表连接起来。

with unpivot_x(student_id,class_id,subject_code,marks) as (
    select * from marklist
    unpivot (marks for subject_code in (  subject_1 as 'subject_1',
                                          subject_2 as 'subject_2',
                                          subject_3 as 'subject_3',
                                          subject_4 as 'subject_4',
                                          subject_5 as 'subject_5'
                                      )
                                      ))
select a.column_name,a.subject_name, count(b.student_id)
  from subject_names a left outer join unpivot_x b
    on a.column_name = b.subject_code and b.class_id = 9
 group by a.column_name,a.subject_name
 order by 1;

演示在 sqlfiddle .