MySQL(Xampp)中如何按逗号拼接SQL列值且遇NULL终止？

Solution for MySQL (XAMPP) #

To concatenate column values with ', ' as the separator and stop immediately when encountering the first NULL value, you can use a CASE statement that checks columns from left to right. This ensures we only include values up to the first NULL and ignore everything after it.

Example Query

Assuming your table is named your_table, here's the query that will produce the desired result:

SELECT
  CASE
    -- Handle cases where the first column is NULL
    WHEN c1s IS NULL THEN ''
    -- First column is valid, check second column
    WHEN c2s IS NULL THEN c1s
    -- First two columns are valid, check third
    WHEN c3s IS NULL THEN CONCAT_WS(', ', c1s, c2s)
    -- First three columns are valid, check fourth
    WHEN c4s IS NULL THEN CONCAT_WS(', ', c1s, c2s, c3s)
    -- First four columns are valid, check fifth
    WHEN c5s IS NULL THEN CONCAT_WS(', ', c1s, c2s, c3s, c4s)
    -- First five columns are valid, check sixth
    WHEN c6s IS NULL THEN CONCAT_WS(', ', c1s, c2s, c3s, c4s, c5s)
    -- First six columns are valid, check seventh
    WHEN c7s IS NULL THEN CONCAT_WS(', ', c1s, c2s, c3s, c4s, c5s, c6s)
    -- All columns are non-NULL, concatenate all
    ELSE CONCAT_WS(', ', c1s, c2s, c3s, c4s, c5s, c6s, c7s)
  END AS concatenated_result
FROM your_table;

How It Works

• We use CASE to check columns in left-to-right order (from c1s to c7s).
• As soon as we hit the first NULL column, we return the concatenation of all previous non-NULL columns using CONCAT_WS (which automatically handles the separator without extra trailing commas).
• If all columns are non-NULL, we concatenate all 7 columns.
• If the first column (c1s) is NULL, we return an empty string—you can adjust this to NULL if that fits your requirements better.

Testing with Your Sample Data

For the row with values a | b | c | d | null | f | g, the query will match the WHEN c5s IS NULL condition and return a, b, c, d exactly as you need.

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