SQL - CASE WHEN 计算不同的值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/23078647/
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
SQL - CASE WHEN count different values
提问by JennaHO88
I need to show how many different values every 'id' has.
我需要显示每个“id”有多少个不同的值。
It should look like this:
它应该是这样的：
id | component_a | component_b | component_c
--------------------------------------------------
KLS11 | none | one | none
KLS12 | one | one | none
KLS13 | several | one | none
KLS14 | one | one | one
KLS15 | one | several | several
I have the following table (table_a):
我有下表（table_a）：
id | component_a | component_b | component_c
--------------------------------------------------
KLS11 | | a |
KLS12 | a | a |
KLS13 | a | a |
KLS13 | b | a |
KLS14 | a | a | a
KLS15 | a | a | a
KLS15 | a | b | b
Here an example/explanation:
这里有一个例子/解释：
- KLS13 has different values in component_a ( a,b ) - so it should display 'several'
- KLS13 has the same values in component_b ( a,a ) - so it should display 'one'
- KLS13 has no value in component_c - so it should display 'none'
- KLS13 在 component_a ( a,b ) 中有不同的值 - 所以它应该显示“几个”
- KLS13 在 component_b ( a,a ) 中具有相同的值 - 所以它应该显示 ' one'
- KLS13 在 component_c 中没有值 - 所以它应该显示“ none”
Here's my SQL-code:
这是我的 SQL 代码：
I already did it for component_a but it doesnt work. What am i doing wrong?
我已经为 component_a 做了它，但它不起作用。我究竟做错了什么？
SELECT
CASE WHEN component_a is NULL THEN 'none'
WHEN (SELECT count(DISTINCT component_a)
FROM table_a
WHERE id=(SELECT id
FROM table_a GROUP BY id HAVING count(*)>1)>1 THEN 'several'
WHEN (SELECT count(DISTINCT component_a)
FROM table_a
WHERE id=(SELECT id
FROM table_a GROUP BY id HAVING count(*)>1)=1 THEN 'one'
END as componentA
FROM table_a
i am a beginner at SQL so i would appreciate any help.
我是 SQL 的初学者，所以我将不胜感激。
Have a nice day
祝你今天过得愉快
回答by Alex Poole
You're getting an ORA-00936 error (I think) because you aren't closing the parentheses within each whenbranch; adding an extra close changes the error to 'ORA-01427: single-row subquery returns more than one row', because the sub-sub-select (with the havingclause) returns multiple rows - there's no correlation.
您收到 ORA-00936 错误（我认为），因为您没有关闭每个when分支中的括号；添加一个额外的关闭将错误更改为“ORA-01427：单行子查询返回多于一行”，因为子子选择（带有having子句）返回多行 - 没有相关性。
You don't need the sub-queries, you just need to count the distinct values as part of the caseconstruct, to create a searched case expression:
您不需要子查询，您只需要将不同的值作为case构造的一部分进行计数，以创建搜索的 case 表达式：
select id,
case count(distinct component_a)
when 0 then 'none'
when 1 then 'one'
else 'several'
end as component_a
from table_a
group by id
order by id;
ID COMPONENT_A
----- -----------
KLS11 none
KLS12 one
KLS13 several
KLS14 one
KLS15 one
And repeat for the other columns:
并重复其他列：
select id,
case count(distinct component_a)
when 0 then 'none'
when 1 then 'one'
else 'several'
end as component_a,
case count(distinct component_b)
when 0 then 'none'
when 1 then 'one'
else 'several'
end as component_b,
case count(distinct component_c)
when 0 then 'none'
when 1 then 'one'
else 'several'
end as component_c
from table_a
group by id
order by id;
ID COMPONENT_A COMPONENT_B COMPONENT_C
----- ----------- ----------- -----------
KLS11 none one none
KLS12 one one none
KLS13 several one none
KLS14 one one one
KLS15 one several several
回答by Hamidreza
Try this query:
试试这个查询：
WITH t1 as
(SELECT COUNT(DISTINCT COMPONENT_A) COMPONENT_A,
COUNT(DISTINCT COMPONENT_B) COMPONENT_B,COUNT(DISTINCT COMPONENT_C) COMPONENT_C
FROM TABLE1 GROUP BY ID)
SELECT
CASE
WHEN COMPONENT_A = 1 THEN 'one'
WHEN COMPONENT_A > 1 THEN 'several' ELSE 'none' END AS COMPONENT_A,
CASE
WHEN COMPONENT_B = 1 THEN 'one'
WHEN COMPONENT_B > 1 THEN 'several' ELSE 'none' END AS COMPONENT_B,
CASE
WHEN COMPONENT_C = 1 THEN 'one'
WHEN COMPONENT_C > 1 THEN 'several' ELSE 'none' END AS COMPONENT_C
FROM t1;
