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