代码之家 › 专栏 › 技术社区 › Alex P

SQL检索具有计算列的聚合数据

sql-server-2005 sql

1

Alex P · 技术社区 · 16 年前

我有一张这样的桌子,可以容纳大约30个学生:

StudentID    Course*      CourseStatus
1            Math         Pass
1            English      Fail
1            Science      Pass
2            Math         Fail
2            English      Pass
2            Science      Fail
etc.

我想用SQL编写一个查询(存储过程),总结给定课程的性能并返回以下内容:

EXEC usp_GetCourseSummary 'Math'

Total Students     Total Pass    % Pass    Total Fail    % Fail
25                 15            60        10            40

1 回复 | 直到 16 年前

1

3

Mark Byers 16 年前

试试这个:

CREATE PROCEDURE usp_GetCourseSummary
    @Course nvarchar(100)
AS     
    SET NOCOUNT ON;
    SELECT
        COUNT(StudentId) AS [Total Students],
        SUM(CASE WHEN CourseStatus = 'Pass' THEN 1 ELSE 0 END) AS [Total Pass],
        SUM(CASE WHEN CourseStatus = 'Pass' THEN 1 ELSE 0 END) * 100 / COUNT(*) AS [% Pass],
        SUM(CASE WHEN CourseStatus = 'Fail' THEN 1 ELSE 0 END) AS [Total Fail], 
        SUM(CASE WHEN CourseStatus = 'Fail' THEN 1 ELSE 0 END) * 100 / COUNT(*) AS [% Fail]
    FROM table1
    WHERE Course = @Course;
GO

EXEC usp_GetCourseSummary 'Math'

结果:

Total Students  Total Pass  % Pass  Total Fail  % Fail
2               1           50      1           50

CREATE TABLE Table1 (StudentID INT NOT NULL, Course NVARCHAR(100) NOT NULL, CourseStatus NVARCHAR(100) NOT NULL);
INSERT INTO Table1 (StudentID, Course, CourseStatus) VALUES
(1, 'Math', 'Pass'),
(1, 'English', 'Fail'),
(1, 'Science', 'Pass'),
(2, 'Math', 'Fail'),
(2, 'English', 'Pass'),
(2, 'Science', 'Fail');