使用CASE WHEN与GROUP BY的SQL如何避免空行？现有查询含无效空行

Great question! The empty rows you're seeing are likely IDs where none of the param_1, param_2, or param_3 values exist (or all their corresponding pValue entries are NULL). Instead of wrapping your query in a subquery, you have two cleaner, more efficient options to filter these out directly with your existing GROUP BY logic:

1. Use the HAVING Clause (Most Direct Solution) #

The HAVING clause is built to filter results after aggregation (unlike WHERE, which filters before). This lets you skip nested subqueries entirely and filter your transposed results directly:

SELECT 
    v.ID,
    MAX(CASE WHEN v.param = 'param_1' THEN v.pValue END) AS param1,
    MAX(CASE WHEN v.param = 'param_2' THEN v.pValue END) AS param2,
    MAX(CASE WHEN v.param = 'param_3' THEN v.pValue END) AS param3
FROM datasource v
GROUP BY v.ID
-- Adjust the condition to match your needs:
-- Keep rows where at least one param is non-null:
HAVING param1 IS NOT NULL OR param2 IS NOT NULL OR param3 IS NOT NULL;
-- Or if you specifically need param1 to be non-null:
-- HAVING param1 IS NOT NULL;

Why this works: #

• HAVING lets you reference the aliased columns from your SELECT clause directly, so you don’t have to repeat the CASE logic.
• It avoids the overhead of creating a temporary subquery result set, making it more efficient than nested queries in most scenarios.

2. Filter Early with WHERE + HAVING (Better Performance for Large Datasets) #

If your datasource table has lots of rows with params outside of param_1, param_2, or param_3, filter those out first with a WHERE clause to reduce the number of rows processed during grouping:

SELECT 
    v.ID,
    MAX(CASE WHEN v.param = 'param_1' THEN v.pValue END) AS param1,
    MAX(CASE WHEN v.param = 'param_2' THEN v.pValue END) AS param2,
    MAX(CASE WHEN v.param = 'param_3' THEN v.pValue END) AS param3
FROM datasource v
-- Only include rows with the params we care about
WHERE v.param IN ('param_1', 'param_2', 'param_3')
GROUP BY v.ID
-- Filter out IDs that still have no non-null values after aggregation
HAVING param1 IS NOT NULL OR param2 IS NOT NULL OR param3 IS NOT NULL;

Why this works: #

• Reducing the dataset size before grouping cuts down on the computation needed for MAX() and GROUP BY, which can speed up queries on large tables significantly.
• Combining WHERE and HAVING ensures you only process relevant data, then filter out any remaining empty rows from the aggregation.

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