查询性能调优-搜索其他选项

Question

我需要知道是否存在另一种方式来执行此查询。

表EXAMS具有以下结构：

EXAM_DATE DATE
SUBJECT VARCHAR2(50);
GRADE NUMBER;

这个概念是从考试中获得统计数据。

select EXAM_DATE,
        SUBJECT,        
        (SELECT COUNT(1) 
            from EXAMS
          where GRADE IN (9,10) 
            AND SUBJECT = EXA.SUBJECT 
            AND EXAM_DATE = EXA.EXAM_DATE) outstanding,
        (select count(1)
            from EXAMS 
          where GRADE IN (4,5,6,7,8) 
            AND SUBJECT = EXA.SUBJECT 
            AND EXAM_DATE = EXA.EXAM_DATE) approved,
        (select count(1)
            from EXAMS
          where GRADE IN (0,1,2,3) 
            AND SUBJECT = EXA.SUBJECT 
            AND EXAM_DATE = EXA.EXAM_DATE) disapproved,
FROM EXAMS EXA
GROUP BY EXAM_DATE,SUBJECT;

谢谢！！

Answer 1

只需使用条件聚合即可。不需要子查询：

select EXAM_DATE, SUBJECT,        
       SUM(GRADE IN (9,10) THEN 1 ELSE 0 END) as outstanding,
       SUM(GRADE IN (4,5,6,7,8) THEN 1 ELSE 0 END) as approved,
       SUM(GRADE IN (0,1,2,3) THEN 1 ELSE 0 END) as disapproved,
FROM EXAMS EXA
GROUP BY EXAM_DATE, SUBJECT;

Answer 2

当您将列中的值(gradeNumber)转换为一行中的值(渐变者计数)时，我可能会使用支点来获得结果。试试这个：

-- make and fill an example table
DECLARE @Exams TABLE
( Exam_Date     DATE
 ,[Subject]     VARCHAR(250)
 ,GradeNumber   INT)

INSERT @Exams VALUES ('2014-01-15','Escapology',1)
INSERT @Exams VALUES ('2014-01-15','Escapology',9)
INSERT @Exams VALUES ('2014-01-15','Escapology',5)
INSERT @Exams VALUES ('2014-01-15','Escapology',3)
INSERT @Exams VALUES ('2014-01-16','Art',8)
INSERT @Exams VALUES ('2014-01-16','Art',7)
INSERT @Exams VALUES ('2014-01-16','Chemistry',0)
INSERT @Exams VALUES ('2014-01-16','Chemistry',5)
INSERT @Exams VALUES ('2014-01-16','Chemistry',9)
INSERT @Exams VALUES ('2014-01-16','Chemistry',2)
INSERT @Exams VALUES ('2014-01-17','Maths',5)
INSERT @Exams VALUES ('2014-01-17','Maths',6)
INSERT @Exams VALUES ('2014-01-18','Geology',10)

-- set up the grade boundaries
DECLARE @A INT = 9
DECLARE @B INT = 4
DECLARE @C INT = 0

-- get the count of grades by exam and date
SELECT * FROM
(SELECT
     Exam_Date
     -- list the exam date twice as one will be used to make the count
    ,Exam_Date  AS ExamDate
    ,[Subject]
    ,CASE WHEN GradeNumber >= @A THEN 'Outstanding'
        WHEN GradeNumber >= @B THEN 'Approved'
        ELSE 'Disapproved' END AS Result
FROM @Exams) AS Up
PIVOT (COUNT(Exam_Date) FOR Result IN ([Outstanding],[Approved],[Disapproved])) AS PVT