如何用单SQL查询定位翻译表中NULL值的列名及对应行？

Hey there! Let's solve this problem so you can get all the NULL-containing column names paired with their corresponding en values in one go.

The Core Idea #

We need to:

1. Check each language column (de, fr) for NULL values per row
2. Collect the names of columns that are NULL for each en entry
3. Aggregate those column names into a single, formatted string for each en value

Query Implementation #

The approach uses a subquery to "unpivot" the NULL checks into individual rows, then aggregates those rows to get the formatted list of NULL columns. Here's how it works for different databases:

For MySQL/MariaDB

SELECT 
    en,
    GROUP_CONCAT(col_name SEPARATOR ' | ') AS null_columns
FROM (
    -- Check for NULLs in the German column
    SELECT 
        en,
        CASE WHEN de IS NULL THEN 'de' END AS col_name
    FROM text
    UNION ALL
    -- Check for NULLs in the French column
    SELECT 
        en,
        CASE WHEN fr IS NULL THEN 'fr' END AS col_name
    FROM text
) AS subquery
WHERE col_name IS NOT NULL -- Filter out non-NULL column checks
GROUP BY en
ORDER BY en;

For PostgreSQL

Use STRING_AGG instead of GROUP_CONCAT:

SELECT 
    en,
    STRING_AGG(col_name, ' | ') AS null_columns
FROM (
    SELECT 
        en,
        CASE WHEN de IS NULL THEN 'de' END AS col_name
    FROM text
    UNION ALL
    SELECT 
        en,
        CASE WHEN fr IS NULL THEN 'fr' END AS col_name
    FROM text
) AS subquery
WHERE col_name IS NOT NULL
GROUP BY en
ORDER BY en;

For Oracle

Use LISTAGG for aggregation:

SELECT 
    en,
    LISTAGG(col_name, ' | ') WITHIN GROUP (ORDER BY col_name) AS null_columns
FROM (
    SELECT 
        en,
        CASE WHEN de IS NULL THEN 'de' END AS col_name
    FROM text
    UNION ALL
    SELECT 
        en,
        CASE WHEN fr IS NULL THEN 'fr' END AS col_name
    FROM text
) AS subquery
WHERE col_name IS NOT NULL
GROUP BY en
ORDER BY en;

How This Works #

• The subquery uses UNION ALL to split each column's NULL check into a separate row. For example, the row with en='other' will generate two rows in the subquery: one for de being NULL, and one for fr being NULL.
• We filter out rows where col_name is NULL (these correspond to columns that had valid values, not NULLs).
• Finally, we group by en and aggregate the col_name values into a single string separated by |, which matches your desired output format.

Result for Your Sample Data #

Running this query on your sample table will produce exactly what you want:

If you add more language columns later (like es for Spanish), just add another UNION ALL block checking that column for NULLs—this solution scales easily!

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