SQL实现：当依赖列存在Null值时将目标列设为Null的语法错误排查（运动员健身时长统计场景）

Hey there! Let's walk through what's wrong with your current query and get you a working solution that meets your requirement.

What's Wrong With Your Original Query? #

You've got two main issues here:

1. Incorrect CASE Expression Syntax: Your CASE statement uses then total_hours = null — this isn't valid SQL. The THEN clause should return a value directly, not an assignment. To return null, you just write THEN NULL.
2. Invalid Column Reference in Outer Query: Your outer query tries to reference daily_hours, but this column isn't included in your subquery's grouped results (you only selected name and sum(daily_hours)). Most databases will throw an error here because daily_hours isn't aggregated or part of the GROUP BY clause.

Correct SQL Implementations #

We need to first check if any daily_hours value is null for each athlete, then use that check to decide whether to return the total hours or null. Here are two clean ways to do this:

Option 1: Using COUNT Comparison (Most Concise)

This leverages the fact that COUNT(daily_hours) ignores null values, while COUNT(*) counts all rows. If they don't match, there's at least one null entry:

SELECT 
    name,
    CASE 
        WHEN COUNT(daily_hours) != COUNT(*) THEN NULL 
        ELSE SUM(daily_hours) 
    END AS total_hours
FROM athletes
GROUP BY name;

Option 2: Explicit Null Check with Aggregation

If you prefer a more explicit approach, you can add a flag in a subquery to track if nulls exist, then use that flag in the outer query:

SELECT 
    name,
    CASE WHEN has_null_entry = 1 THEN NULL ELSE total_hours END AS total_hours
FROM (
    SELECT 
        name,
        SUM(daily_hours) AS total_hours,
        -- Flag = 1 if any daily_hours is null, 0 otherwise
        MAX(CASE WHEN daily_hours IS NULL THEN 1 ELSE 0 END) AS has_null_entry
    FROM athletes
    GROUP BY name
) AS athlete_summary;

How These Work #

• Both queries group by athlete name to calculate totals per person.
• The null check ensures that if even one day's daily_hours is null, the final total_hours becomes null instead of the sum of non-null days.
• No subquery is strictly necessary for the first option, making it more efficient and readable for most use cases.

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