代码之家 › 专栏 › 技术社区 › T la

SQL Pivot课程注册学生成绩

oracle-sqldeveloper pivot sql

0

T la · 技术社区 · 8 年前

我在SQL中有以下结果数据表(我没有共享大约200行):

DEPT     COURSE#    COURSE NAME             QTY ENROLLED   GRADE
----    --------    -------------           ------------   -----
ACCT    ACCT2220    ACCOUNTING II           50              B
ACCT    ACCT2220    ACCOUNTING II           36              C
ACCT    ACCT2220    ACCOUNTING II           2               D
ACCT    ACCT2220    ACCOUNTING II           9               F
CHEM    CHEM2001    INTRO TO CHEMISTRY      17              A
CHEM    CHEM2001    INTRO TO CHEMISTRY      50              B
CHEM    CHEM2001    INTRO TO CHEMISTRY      36              C
COSC    COSC1310    INTRO TO COMPUTER SCI   50              B
COSC    COSC1310    INTRO TO COMPUTER SCI   36              C

我想围绕这一点,使其看起来类似于以下内容:

DEPT  COURSE#     COURSE NAME   A    B    C   D   F
----  -------   ------------- ---  ---  --- --- ---
ACCT  ACCT2220  ACCOUNTING II   0   50   36  2    9
CHEM  CHEM2001  INTRO TO CHEM   17  50   36  0    0
COSC  COSC1310  INTRO TO COMP   0   50   36  0    0

我当前的查询(凌乱):

SELECT
course.offering_dept, course.course_number, course.course_name,
count(grade_report.student_number), grade_report.grade

FROM
eb.course,
eb.grade_report JOIN eb.section ON grade_report.section_id = 
section.section_id

GROUP BY
course.offering_dept, course.course_number, course.course_name, 
grade_report.grade

ORDER BY
course.course_number;

我似乎不知道该怎么办。每次我使用PIVOT时,都不会运行任何东西,并且我会错误地说出无效标识符。如有任何想法或建议,我们将不胜感激。

1 回复 | 直到 8 年前

1

Pà®°à®¤à¯à®ªà¯ 8 年前

如果您使用的是Sql Server,这里有一种使用Pivot的方法

SELECT course.offering_dept,
        course.course_number,
        course.course_name,
        [A],[B],[C],[D],[F]
FROM   eb.course,
        eb.grade_report
        JOIN eb.section
            ON grade_report.section_id = section.section_id
PIVOT (Count(grade_report.student_number)
        FOR GRADE IN ([A],[B],[C],[D],[F])) pv

其他的

SELECT course.offering_dept,
       course.course_number,
       course.course_name,
       sum(CASE WHEN GRADE = 'A' THEN 1 ELSE 0 END) as 'A',
       sum(CASE WHEN GRADE = 'B' THEN 1 ELSE 0 END) as 'B',
       sum(CASE WHEN GRADE = 'C' THEN 1 ELSE 0 END) as 'C',
       sum(CASE WHEN GRADE = 'D' THEN 1 ELSE 0 END) as 'D',
       sum(CASE WHEN GRADE = 'F' THEN 1 ELSE 0 END) as 'F'
FROM   eb.course,
       eb.grade_report
       JOIN eb.section
         ON grade_report.section_id = section.section_id
GROUP  BY course.offering_dept,
          course.course_number,
          course.course_name
ORDER  BY course.course_number;