SQL -当计数不同的值时

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):

我有下表(表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在component_a (a,b)中有不同的值，因此它应该显示“几个”
KLS13 has the same values in component_b ( a,a ) - so it should display 'one'
KLS13在component_b (a,a)中具有相同的值——因此应该显示“1”
KLS13 has no value in component_c - so it should display 'none'
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

祝你有美好的一天

2 个解决方案

#1

You're getting an ORA-00936 error (I think) because you aren't closing the parentheses within each when branch; 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 having clause) returns multiple rows - there's no correlation.

你会得到一个ORA-00936错误(我认为)因为你没有关闭每个分支中的圆括号;添加一个额外的close将错误更改为“ORA-01427:单行子查询返回多于一行”，因为子选择(带有had子句)返回多个行——没有相关性。

You don't need the sub-queries, you just need to count the distinct values as part of the case construct, 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

#2

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;

#1