SQL分组透视表实现学生任务完成情况统计问询

Hey there! Let's walk through exactly how to build that grouped pivot table you need, with two solid approaches to get the exact output you're targeting.

First, let's confirm the source table structure and data we're working with (I'll define it explicitly so you can test easily):

-- Create sample table
CREATE TABLE StudentTasks (
    Name VARCHAR(50),
    RequirementID INT,
    RequirementType VARCHAR(50),
    Completed INT NULL
);

-- Insert your sample data
INSERT INTO StudentTasks VALUES
('Fred', 1, 'Questions', 1),
('Fred', 2, 'Portfolio', NULL),
('Fred', 3, 'Questions', NULL),
('Fred', 4, 'Portfolio', 1),
('Mary', 1, 'Questions', NULL),
('Mary', 2, 'Portfolio', 1),
('Mary', 3, 'Questions', 1),
('Mary', 4, 'Portfolio', 1);

Solution 1: Universal CASE WHEN Approach (Works for All Databases) #

This method is the most flexible—it works in MySQL, PostgreSQL, SQL Server, Oracle, and more. We'll use conditional aggregation to pivot the data manually.

SELECT
    Name,
    COUNT(RequirementID) AS TotalRequired,
    -- Count completed Questions tasks
    SUM(CASE 
        WHEN RequirementType = 'Questions' AND Completed = 1 THEN 1 
        ELSE 0 
    END) AS Questions,
    -- Count completed Portfolio tasks
    SUM(CASE 
        WHEN RequirementType = 'Portfolio' AND Completed = 1 THEN 1 
        ELSE 0 
    END) AS Portfolio
FROM StudentTasks
GROUP BY Name
ORDER BY Name;

Breakdown of this query: #

• COUNT(RequirementID): Gets the total number of tasks per student (since every row is a unique task, this counts all required items regardless of completion status).
• The SUM(CASE...) clauses: For each task type, we check if it's completed (Completed = 1). If yes, we add 1 to the sum; if not, we add 0. This gives us the count of completed tasks per type.

Solution 2: PIVOT Keyword (SQL Server/Oracle Specific) #

If you're using a database that supports the PIVOT operator (like SQL Server or Oracle), you can write a more concise query. This method first aggregates the data by student and task type, then pivots the types into columns.

SELECT
    Name,
    TotalRequired,
    ISNULL(Questions, 0) AS Questions,
    ISNULL(Portfolio, 0) AS Portfolio
FROM (
    -- Subquery to get per-student total tasks and per-type completed counts
    SELECT
        Name,
        RequirementType,
        COUNT(RequirementID) OVER(PARTITION BY Name) AS TotalRequired,
        SUM(CASE WHEN Completed = 1 THEN 1 ELSE 0 END) AS CompletedCount
    FROM StudentTasks
    GROUP BY Name, RequirementType
) AS TaskStats
-- Pivot the RequirementType values into columns
PIVOT (
    MAX(CompletedCount)
    FOR RequirementType IN ([Questions], [Portfolio])
) AS PivotTable
ORDER BY Name;

Breakdown of this query: #

• The subquery uses COUNT(...) OVER(PARTITION BY Name) to calculate the total tasks per student without grouping by it. We also group by Name and RequirementType to get completed counts for each task type.
• The PIVOT clause converts the RequirementType values (Questions, Portfolio) into separate columns, using MAX(CompletedCount) (since each student-type group has only one count value, MAX/MIN would work here).
• ISNULL() ensures we get 0 instead of NULL if a student has no completed tasks for a type (though in your sample data, every student has both types, this adds robustness for edge cases).

Final Result #

Both queries will return exactly the output you need:

内容的提问来源于stack exchange，提问作者gillers322