Concat中的Group_Concat不使用NULL值

时间:2021-09-02 11:50:14

I have a table

我有一张桌子

CREATE TABLE IF NOT EXISTS `dept` (
  `did` int(11) NOT NULL,
  `dname` varchar(50) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `dept` (`did`, `dname`) VALUES
(1, 'Hi'),
(2, NULL),
(3, 'Hello');

Then I have a query

然后我有一个查询

select group_concat(concat(did,"','",dname) separator '),(') as Result from dept

It is producing result as 1','Hi'),('3','Hello

它产生的结果为1','Hi'),('3','你好

Question: How can I get result from above query as 1','Hi'),('2','NULL'),('3','Hello

问题:如何从上面的查询得到1','Hi'),('2','NULL'),('3','你好'的结果

It is missing the rows which have NULL values but I need to fetch all

它缺少具有NULL值但我需要获取所有值的行

Link for SQL Fiddle Demo of question

链接SQL小提琴演示的问题

UPDATE: If I have more than one or all columns allowing NULL, Is there some way to apply COALESCE once for all or have to apply individually at each Column?

更新:如果我有多个或所有列允许NULL,是否有一些方法可以一次性应用COALESCE或者必须在每个列单独应用?

3 个解决方案

#1


9  

try this, use COALESCE

试试这个,使用COALESCE

.., COALESCE(dname, 'NULL'),..

making it NULL string visible. SQLFIDDLE DEMO

使其字符串可见。 SQLFIDDLE DEMO

#2


2  

From the MySQL aggregate function documentation:

从MySQL聚合函数文档:

Unless otherwise stated, group functions ignore NULL values.

除非另有说明,否则组函数会忽略NULL值。

Use COALESCE() to replace the nulls with a string, since they would be eliminated by the aggregate function. For example COALESCE(dbname, 'NULL') will return the string NULL if dbname IS NULL. Its purpose is to return the first non-null of the arguments you give it, and can therefore return a default value.

使用COALESCE()将空值替换为字符串,因为它们将被聚合函数消除。例如,如果dbname为IS NULL,则COALESCE(dbname,'NULL')将返回字符串NULL。它的目的是返回您给它的参数的第一个非null,因此可以返回默认值。

SELECT
  GROUP_CONCAT(CONCAT(did,"','", COALESCE(dname, 'NULL')) SEPARATOR "'),('") AS Result
FROM dept

#3


0  

Hope following query will serve your purpose

希望以下查询将满足您的目的

SELECT GROUP_CONCAT(
IF(dname IS NOT NULL, CONCAT(did,"','",dname), CONCAT(did,"','NULL")) 
SEPARATOR '),(') AS Result FROM dept

#1


9  

try this, use COALESCE

试试这个,使用COALESCE

.., COALESCE(dname, 'NULL'),..

making it NULL string visible. SQLFIDDLE DEMO

使其字符串可见。 SQLFIDDLE DEMO

#2


2  

From the MySQL aggregate function documentation:

从MySQL聚合函数文档:

Unless otherwise stated, group functions ignore NULL values.

除非另有说明,否则组函数会忽略NULL值。

Use COALESCE() to replace the nulls with a string, since they would be eliminated by the aggregate function. For example COALESCE(dbname, 'NULL') will return the string NULL if dbname IS NULL. Its purpose is to return the first non-null of the arguments you give it, and can therefore return a default value.

使用COALESCE()将空值替换为字符串,因为它们将被聚合函数消除。例如,如果dbname为IS NULL,则COALESCE(dbname,'NULL')将返回字符串NULL。它的目的是返回您给它的参数的第一个非null,因此可以返回默认值。

SELECT
  GROUP_CONCAT(CONCAT(did,"','", COALESCE(dname, 'NULL')) SEPARATOR "'),('") AS Result
FROM dept

#3


0  

Hope following query will serve your purpose

希望以下查询将满足您的目的

SELECT GROUP_CONCAT(
IF(dname IS NOT NULL, CONCAT(did,"','",dname), CONCAT(did,"','NULL")) 
SEPARATOR '),(') AS Result FROM dept