SQL查询需求：筛选所有line_number值均为NULL的item_number

Got it, let's break this down step by step. First, here's the DDL to create a table with sample data that matches your exact scenario (3 unique items, each with 3 lines; only item 2 has all NULL line numbers):

CREATE TABLE item_lines (
    item_number INT,
    line_number INT NULL
);

-- Insert sample data matching your description
INSERT INTO item_lines (item_number, line_number)
VALUES
    (1, 1),
    (1, 2),
    (1, NULL),
    (2, NULL),
    (2, NULL),
    (2, NULL),
    (3, NULL),
    (3, 3),
    (3, NULL);

Now, here are two reliable ways to query for items where all line_number values are NULL:

Method 1: GROUP BY + COUNT (Simple & Efficient) #

Since COUNT(column) automatically ignores NULL values, if all line_number entries for an item are NULL, the count will be 0. This is probably the most straightforward approach:

SELECT item_number
FROM item_lines
GROUP BY item_number
HAVING COUNT(line_number) = 0;

Method 2: NOT EXISTS (Explicit Logic) #

This method checks for items where there are no rows with a non-NULL line_number, which directly translates your requirement into SQL:

SELECT DISTINCT item_number
FROM item_lines il
WHERE NOT EXISTS (
    SELECT 1
    FROM item_lines il2
    WHERE il2.item_number = il.item_number
      AND il2.line_number IS NOT NULL
);

Both queries will return item_number = 2 as the result, which is exactly what you need.

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