执行查询后替换内容

f0ofjuux 于 2021-06-19 发布在 Mysql

关注(0)|答案(2)|浏览(270)

我需要将所有可能等于“0”的结果替换为空值。最好的选择是什么？在这种情况下，请使用case语句或replace命令：

SELECT name, COUNT(IF(stat='open',1, NULL)) 'open',
       COUNT(IF(stat='close',1, NULL)) 'close',
       COUNT(IF(stat='all',1, NULL)) 'all',
       COUNT(IF(stat='reopen',1, NULL)) 'reopen',
       COUNT(IF(stat='finish',1, NULL)) 'finish'
FROM dashboard group by name order by name = 'Party' desc

+----------+------+-------+-----+--------+--------+
| name     | open | close | all | reopen | finish |
+----------+------+-------+-----+--------+--------+
| Party    |   21 |     0 |   0 |      0 |      0 |
+----------+------+-------+-----+--------+--------+

sql mysql

来源：https://stackoverflow.com/questions/53310130/replace-content-after-do-a-query

2条答案

按热度按时间

amrnrhlw1#

你可以用 NULLIF() 功能。

SELECT name, 
       NULLIF(COUNT(IF(stat='open',1, NULL)), 0) 'open',
       NULLIF(COUNT(IF(stat='close',1, NULL)), 0) 'close',
       NULLIF(COUNT(IF(stat='all',1, NULL)), 0) 'all',
       NULLIF(COUNT(IF(stat='reopen',1, NULL)), 0) 'reopen',
       NULLIF(COUNT(IF(stat='finish',1, NULL)), 0) 'finish'
FROM dashboard 
group by name 
order by name = 'Party' desc
``` `NULLIF()` 返回第一个值，除非它等于第二个值，否则返回 `NULL` .
顺便说一句，你可以简化 `COUNT(IF(stat='', 1, NULL))` 至 `SUM(stat='')` .

赞(0）回复(0）举报 2021-06-20

uqzxnwby2#

一种方法使用 NULLIF() :

SELECT name,
       NULLIF(SUM(stat = 'open'), 0) as open,
       NULLIF(SUM(stat = 'close'), 0) as close,
       NULLIF(SUM(stat = 'all'), 0) as all,
       NULLIF(SUM(stat = 'reopen'), 0) as reopen,
       NULLIF(SUM(stat = 'finish'), 0) as finish
FROM dashboard 
GROUP BY name 
ORDER BY name = 'Party' desc;

其他用途 CASE :

SELECT SUM(CASE WHEN stat = 'open' THEN 1 END) as open,
       . . .

赞(0）回复(0）举报 2021-06-20

我来回答

执行查询后替换内容

2条答案

相关问题

热门标签

最新问答