如何在SQL查询中显示缺失category_id的空行，完整展示考试结果

Hey there! Let's work through your two SQL needs together—handling missing values with nulls/empty rows and ensuring every subject shows both required category_id entries.

1. Core Approach: Generate All Required Subject-Category Pairs #

The key here is to first create a complete set of all possible combinations of subject_id and your two target category_id values. Then we'll left-join this with your aggregated exam results to fill in existing data and show nulls where no records exist.

Step-by-Step SQL Implementation #

First, let's fix the typo in your query (catagory → category) and build a complete solution:

-- First, define the two category_ids every subject should have
WITH required_categories AS (
    SELECT 1 AS category_id UNION ALL SELECT 2 AS category_id -- replace with your actual category IDs
),
-- Get all unique subjects from your exam results (or subjects table if you have one)
all_subjects AS (
    SELECT DISTINCT subject_id FROM examresult
),
-- Generate every possible subject-category pair
all_subject_category_pairs AS (
    SELECT s.subject_id, c.category_id
    FROM all_subjects s
    CROSS JOIN required_categories c
),
-- Your original aggregated query, cleaned up
aggregated_results AS (
    SELECT 
        er.subject_id,
        er.student_id,
        er.class_id,
        ec.exam_category_name,
        er.category_id,
        er.exam_type_id,
        SUM(er.marks_obtained) AS subject_marks_obtained
        -- Add your other SUM/aggregation columns here
    FROM examresult er
    JOIN examcategory ec ON er.category_id = ec.category_id -- assuming this join exists in your full query
    GROUP BY er.subject_id, er.student_id, er.class_id, ec.exam_category_name, er.category_id, er.exam_type_id
)
-- Final query: Join the full pairs with aggregated data to show all required rows
SELECT 
    p.subject_id,
    ar.student_id,
    ar.class_id,
    ec.exam_category_name, -- Join examcategory again to get name even if no results
    p.category_id,
    ar.exam_type_id,
    COALESCE(ar.subject_marks_obtained, 0) AS subject_marks_obtained -- Use COALESCE if you want 0 instead of null
    -- Add other aggregated columns with COALESCE if needed
FROM all_subject_category_pairs p
LEFT JOIN aggregated_results ar ON p.subject_id = ar.subject_id AND p.category_id = ar.category_id
LEFT JOIN examcategory ec ON p.category_id = ec.category_id -- Get category name even for missing results
ORDER BY p.subject_id, p.category_id;

2. How This Solves Your Two Requirements #

• Missing category_id entries show null: The CROSS JOIN generates every subject-category pair, so even if a subject has no records for one category_id, that pair still exists. The LEFT JOIN keeps those rows, and all aggregated columns will be null (or 0 if you use COALESCE).
• Empty rows for missing values: If a subject has no records at all for either category_id, both pairs will appear in the result, with all data columns (except subject_id and category_id) showing null—effectively creating the "empty rows" you need.

Quick Notes #

• Replace 1 and 2 in required_categories with your actual target category IDs.
• If you have a separate subjects table, use that in all_subjects instead of examresult to include subjects that have no exam results at all.
• Use COALESCE if you prefer to show 0 instead of null for missing marks—adjust based on your display needs.

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