本文介绍了如果任何值为NULL,则GROUP_CONCAT返回NULL的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
如果有任何列为 NULL $ ,我该如何使 GROUPT_CONCAT 返回 NULL c $ c $?
这是一张测试表:
CREATE TABLE gc(a INT(11)NOT NULL,b VARCHAR(1)DEFAULT NULL ); INSERT INTO gc(a,b)VALUES (1,'a'),(1,'b'),(2,'c '),(2,NULL),(3,'e');我的查询:
SELECT a,GROUP_CONCAT(b) FROM gc GROUP BY a;这就是我得到的结果:
a | GROUP_CONCAT(b) - + ---------------- 1 | a,b 2 | c 3 | e这就是我想要的:
a | GROUP_CONCAT(b) - + ---------------- 1 | a,b 2 | NULL 3 | e解决方案
/ code>表达式检查组中是否有NULL值。我可以想到几种方法:
1)计算非空值并将其与组中的行数进行比较:
pre $ SELECT a,IF(COUNT(b)= COUNT(*),GROUP_CONCAT(b),NULL)AS bs FROM gc GROUP BY a查看在线工作: sqlfiddle
使用 SUM : SELECT a,IF(SUM(b IS NULL)= 0 ,GROUP_CONCAT(b),NULL)AS bs FROM gc GROUP BY a看到它在线工作: sqlfiddle
How can I make GROUPT_CONCAT return NULL if any column is NULL?
Here is a test table:
CREATE TABLE gc ( a INT(11) NOT NULL, b VARCHAR(1) DEFAULT NULL ); INSERT INTO gc (a, b) VALUES (1, 'a'), (1, 'b'), (2, 'c'), (2, NULL), (3, 'e');And my query:
SELECT a, GROUP_CONCAT(b) FROM gc GROUP BY a;This is what I get:
a | GROUP_CONCAT(b) --+---------------- 1 | a,b 2 | c 3 | eThis is what I want:
a | GROUP_CONCAT(b) --+---------------- 1 | a,b 2 | NULL 3 | e解决方案
In an IF expression check if any value is NULL in the group. I can think of a couple of ways of doing that:
1) Count the non-null values and compare it to the number of rows in the group:
SELECT a, IF(COUNT(b) = COUNT(*), GROUP_CONCAT(b), NULL) AS bs FROM gc GROUP BY aSee it working online: sqlfiddle
2) Count the number of null values using SUM:
SELECT a, IF(SUM(b IS NULL) = 0, GROUP_CONCAT(b), NULL) AS bs FROM gc GROUP BY aSee it working online: sqlfiddle
更多推荐
如果任何值为NULL,则GROUP
发布评论