带有空值的 MySQL GROUP_CONCAT

MySQL GROUP_CONCAT with Nulls

提问by David

Is there an option to make MySQL's Group_Concat function include nulls?

是否可以选择使 MySQL 的 Group_Concat 函数包含空值？

Consider the following example from my source table:

考虑我的源表中的以下示例：

userId, questionId, selectionId
7, 3, NULL
7, 4, 1
7, 5, 2

When I query on the selection table with GROUP_CONCAT, I get the following:

当我使用 GROUP_CONCAT 查询选择表时，我得到以下信息：

7, 4=1,5=2

I would like to get the following:

我想得到以下内容：

7, 3=NULL,4=1,5=2

For reference, my query looks like this:

作为参考，我的查询如下所示：

Select userId, GROUP_CONCAT(CONCAT(questionId, '=', selectionId))
From selection
Group by userId;

I also tried adding an IFNULL like this:

我也尝试添加一个 IFNULL 像这样：

Select userId, GROUP_CONCAT(IFNULL(CONCAT(questionId, '=', selectionId), 'NULL'))
From selection
Group by userId;

but that produced the following:

但这产生了以下内容：

7, NULL,4=1,5=2

Note - There is one other complexity that I forgot to include. The selectionId is a foreign key to another table. I use a left outer join to the selection_text table. My real query includes fields from that table (these fields resolve to NULL since the selectionId is null).

注意 - 我忘记包括另一种复杂性。selectionId 是另一个表的外键。我对 selection_text 表使用左外连接。我的真实查询包括该表中的字段（这些字段解析为 NULL，因为 selectionId 为空）。

回答by Joachim Isaksson

You should just IFNULLthe column that can be NULL;

您应该只是IFNULL可以是的列NULL；

SELECT userId, GROUP_CONCAT(CONCAT(questionId, '=', 
                 IFNULL(selectionId, 'NULL')))
FROM selection
GROUP BY userId;

Demo here.

演示在这里。

回答by Dmytro Shevchenko

You should use IFNULLor COALESCEon the selectionIdvalue directly:

您应该直接在值上使用IFNULL或：COALESCEselectionId

SELECT
  userId,
  GROUP_CONCAT(CONCAT(questionId, '=', COALESCE(selectionId, 'NULL')))
FROM selection
GROUP BY userId;