Concat中的Group_Concat无法使用NULL值 [英] Group_Concat in Concat not working with NULL values

问题描述

我有一张桌子

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');

然后我有一个查询

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

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

问题:如何从上述查询中以1','Hi'),('2','NULL'),('3','Hello

的形式获取结果

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

问题的SQL Fiddle演示的链接

更新:如果我有一个以上的允许NULL的列，是否有某种方法可以一次全部应用COALESCE或必须在每个列上单独应用?

解决方案

尝试一下，使用COALESCE

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

将其显示为NULL字符串. SQLFIDDLE DEMO

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

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

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

Link for SQL Fiddle Demo of question

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?

解决方案

try this, use COALESCE

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

making it NULL string visible. SQLFIDDLE DEMO

这篇关于Concat中的Group_Concat无法使用NULL值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！