PostgreSQL, SELECT CASE COALESCE
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/27300552/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
PostgreSQL, SELECT CASE COALESCE
提问by Wine Too
I need to 'name' categories: mycatis a textcolumn with possible values '0'to '4'.
我需要“名”类：mycat是一个text可能的值列'0'来'4'。
SELECT CASE mycat
WHEN '0' THEN 'ZERO'
WHEN '1' THEN 'ONE'
WHEN '2' THEN 'TWO'
WHEN '3' THEN 'THREE'
WHEN '4' THEN 'OTHER'
END AS my_category,
COALESCE(SUM(col1), 0),
COALESCE(SUM(col2), 0),
COALESCE(SUM(col3), 0)
FROM mytable
GROUP BY mycat
ORDER BY mycat;
That works OK, but I have some an error in my program which very rarely writes null(or ''as I can see in pgAdmin). In such cases I have to treat that ''the same as '0'.
But I can't get that!
这工作正常，但我的程序中有一些错误，很少写入null（或者''我可以在 pgAdmin 中看到）。在这种情况下，我必须将其''视为与'0'. 但我无法理解！
I try like this:
我像这样尝试：
SELECT CASE COALESCE(mycat, '0')
But this doesn't solve it at all.
How to get that ''will be summed and grouped together with '0'category?
但这根本解决不了问题。
如何''将其与'0'类别相加并分组？
PostgreSQL 9.3, Windows.
PostgreSQL 9.3，Windows。
回答by radar
you need to use COALESCE in the group byand order byalso similar to how you planned to change the caseexpression, but postgres is giving error , so another option is to wrap your statement in a subquery and do group by
您需要在 中使用 COALESCEgroup by并且order by也类似于您计划更改case表达式的方式，但是 postgres 给出了错误，因此另一种选择是将您的语句包装在子查询中并执行group by
SELECT my_category,
COALESCE(SUM(col1), 0),
COALESCE(SUM(col2), 0),
COALESCE(SUM(col3), 0)
FROM
(
SELECT CASE coalesce(mycat ,'0')
WHEN '0' THEN 'ZERO'
WHEN '1' THEN 'ONE'
WHEN '2' THEN 'TWO'
WHEN '3' THEN 'THREE'
WHEN '4' THEN 'OTHER'
WHEN '' THEN 'ZERO'
END AS my_category,
col1,
col2,
col3
FROM mytable
) T
GROUP BY my_category
ORDER BY my_category
回答by Erwin Brandstetter
You can have this without subquery. You could repeat the expression in the GROUP BY and ORDER BY clause. But it's much simplerto use the ordinal number of the output column instead:
您可以在没有子查询的情况下使用它。您可以在 GROUP BY 和 ORDER BY 子句中重复表达式。但是使用输出列的序号要简单得多：
SELECT CASE mycat
WHEN '1' THEN 'ONE'
WHEN '2' THEN 'TWO'
WHEN '3' THEN 'THREE'
WHEN '4' THEN 'OTHER'
ELSE 'ZERO' -- catches all other values
END AS my_category
, COALESCE(SUM(col1), 0) AS sum1
, COALESCE(SUM(col2), 0) AS sum2
, COALESCE(SUM(col3), 0) AS sum3
FROM mytable
GROUP BY 1
ORDER BY 1;
I chose the simplest and fastestcode. The ELSE branch catches 0, ''and NULL- or any other value not yet filtered! But you say there are no others.
我选择了最简单最快的代码。ELSE 分支捕获0,''和NULL- 或任何其他尚未过滤的值！但你说没有其他人。
A couple of rants:
吐槽几句：
mycat is 'text' column with possible values '0' to '4'.
This is wrong in two ways.- You obviously also have empty strings (
'') and / orNULLvalues. integer,smallint,"char"orenumwould be sensible choices for the data type.textis not.
- You obviously also have empty strings (
To find out your actual range of values:
SELECT mycat, count(*) AS ct FROM mytable GROUP BY 1 ORDER BY 2 DESC;pgAdmin displays empty strings a '', but not
NULLwith default settings.
If you are not sure, test withmycat IS NULL. You needto know and understand the difference in many situations.This orders by the resulting text in
my_category?ONE, OTHER, THREE, TWO, ZERO? I doubt you want that. To keep it simple you could instead output:0, 1, 2, 3, OTHER.
mycat is 'text' column with possible values '0' to '4'.
这在两个方面是错误的。- 您显然也有空字符串 (
'') 和/或NULL值。 integer,smallint,"char"或enum将是数据类型的明智选择。text不是。
- 您显然也有空字符串 (
要找出您的实际值范围：
SELECT mycat, count(*) AS ct FROM mytable GROUP BY 1 ORDER BY 2 DESC;pgAdmin 显示空字符串 a ''，但不
NULL显示默认设置。
如果您不确定，请使用mycat IS NULL. 您需要了解并了解许多情况下的差异。这由生成的文本在
my_category?ONE, OTHER, THREE, TWO, ZERO? 我怀疑你想要那个。为了简单起见，您可以改为输出：0, 1, 2, 3, OTHER.
