带空值的MySQL GROUP_CONCAT [英] MySQL GROUP_CONCAT with Nulls

问题描述

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

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

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

Consider the following example from my source table:

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

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

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

7, 4=1,5=2

我想获得以下信息:

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;

我还尝试过这样添加IFNULL:

I also tried adding an IFNULL like this:

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

但是产生了以下内容:

7, NULL,4=1,5=2

注意-我忘了包括另一种复杂性. selectionId是另一个表的外键.我使用左外部联接到selection_text表.我真正的查询包括该表中的字段(由于selectionId为null，因此这些字段解析为NULL.)

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).

推荐答案

您应该IFNULL可以为NULL;

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

演示此处.

这篇关于带空值的MySQL GROUP_CONCAT的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！