求满足特定非空匹配条件的SQL标准查询语句

Alright, let's tackle this SQL query problem based on your specific requirements. Since you mentioned you already adjusted isaace's solution to make it work, I'll frame this answer to align with that approach while making sure it covers all your needs clearly:

First, here's the complete query (replace placeholder names with your actual table/column names):

WITH date_group_stats AS (
    SELECT 
        date_column, -- Replace with your actual date column used for grouping
        COUNT(CASE WHEN fourth_column IS NOT NULL THEN 1 END) AS non_null_count,
        COUNT(*) AS total_rows_in_group
    FROM your_table -- Replace with your table name
    GROUP BY date_column
)
-- Return all rows where the 4th column is NULL
SELECT * FROM your_table WHERE fourth_column IS NULL
UNION ALL
-- Return "AAA"/"BBB" rows where their date group has exactly 1 non-null 4th column entry
SELECT t.*
FROM your_table t
JOIN date_group_stats gs ON t.date_column = gs.date_column
WHERE t.fourth_column IS NOT NULL
  AND t.category_column IN ('AAA', 'BBB') -- Replace with your column that holds "AAA"/"BBB"
  AND gs.non_null_count = 1
  AND gs.non_null_count < gs.total_rows_in_group;

Let's break down the logic step by step:

1. Group-Level Statistics CTE #

The date_group_stats common table expression calculates two key values for each date group:

• non_null_count: Number of rows in the date group where the 4th column is not null
• total_rows_in_group: Total number of rows in the date group

This helps us quickly check if a date group meets your criteria for returning "AAA"/"BBB" rows.

2. First Result Set: All NULL 4th Column Rows #

The first SELECT statement directly grabs every row where the 4th column is NULL—this fulfills your requirement to always return these rows, no exceptions.

3. Second Result Set: Qualifying "AAA"/"BBB" Rows #

The second SELECT statement joins back to our group stats to filter only the rows that meet your conditions:

• We target rows where the 4th column is not null and the category is "AAA" or "BBB"
• We only include rows from date groups where there's exactly 1 non-null entry in the 4th column
• The non_null_count < total_rows_in_group check ensures we exclude date groups where all rows have a non-null 4th column (since we don't want to return those)

• Swap fourth_column with your actual 4th column name
• Replace date_column with the column you use to group by date
• Update category_column to the column that contains the "AAA"/"BBB" values
• Adjust your_table to match your table's name

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