如何从数据表中筛选仅单一列存在有效值的行？

Problem Overview #

Given the following table:

ID | Number | Param1 | Param2 | Param3 | Param4 | Param5
---|--------|--------|--------|--------|--------|-------
1  | null   | null   | null   | null   | null   | XTO10
2  | null   | null   | null   | KMC3   | null   | null
3  | null   | YUP    | null   | null   | null   | null
4  | 103    | VB0    | BJ0    | KL9    | null   | null
5  | null   | FH1    | null   | null   | null   | null
6  | 103    | VB0    | BJ0    | KL9    | null   | null
7  | 103    | null   | null   | KL9    | null   | null
8  | 103    | VB0    | BJ0    | KL9    | AS1    | AM
9  | null   | VB0    | BJ0    | KL9    | AS1    | AM
10 | 99     | HS1    | null   | null   | AS1    | AM

We need to filter rows where exactly one of the columns Number, Param1-Param5 has a non-null value (all others in this set are null). The expected output is:

ID | Number | Param1 | Param2 | Param3 | Param4 | Param5
---|--------|--------|--------|--------|--------|-------
1  | null   | null   | null   | null   | null   | XTO10
2  | null   | null   | null   | KMC3   | null   | null
3  | null   | YUP    | null   | null   | null   | null
5  | null   | FH1    | null   | null   | null   | null

Solution #

The core idea is to count how many non-null values exist in each row (for the target columns) and select rows where that count equals exactly 1. This works in most SQL databases (PostgreSQL, MySQL, SQL Server, etc.):

SELECT *
FROM your_table_name
WHERE 
  -- Count non-null values in Number, Param1-Param5
  (CASE WHEN Number IS NOT NULL THEN 1 ELSE 0 END) +
  (CASE WHEN Param1 IS NOT NULL THEN 1 ELSE 0 END) +
  (CASE WHEN Param2 IS NOT NULL THEN 1 ELSE 0 END) +
  (CASE WHEN Param3 IS NOT NULL THEN 1 ELSE 0 END) +
  (CASE WHEN Param4 IS NOT NULL THEN 1 ELSE 0 END) +
  (CASE WHEN Param5 IS NOT NULL THEN 1 ELSE 0 END) = 1;

How It Works #

• Each CASE statement checks if a column has a non-null value: if yes, it returns 1, otherwise 0.
• Summing these values gives the total number of non-null columns in the row (for the target set).
• The WHERE clause filters rows where this sum equals 1—meaning exactly one column in the set has a valid value, others are null.

Alternative (For Specific SQL Dialects) #

Some databases have shortcut functions to simplify this. For example, in PostgreSQL, you can use cardinality(array_remove(array[Number, Param1, Param2, Param3, Param4, Param5], null)) = 1 instead of the CASE statements. However, the CASE approach is more universal across different SQL systems.

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