依靠案例 Oracle
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/41376878/
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
Count on case Oracle
提问by Anirudh D
WE have below data in oracle database -
我们在oracle数据库中有以下数据-
col1 col2
Z1 A
Z1 B
Z2 A
Z2 C
Z3 A
Z4 D
I want count on column two in such a way that -
我想以这样的方式依靠第二列 -
Ouput -
输出 -
col2 count
A 3 (Z1,Z2,Z3)
B 0 (Dont count if A is already present for record)
C 0
D 1 (Z4)
Best Regards
此致
采纳答案by Anirudh D
Thanks Guys. But I could do this way -
谢谢你们。但我可以这样做-
select count(case
when (LISTAGG(col2,'-') WITHIN GROUP (ORDER BY col2)) like '%A%' then 1
else null
end) A,
count(case
when (LISTAGG(col2,'-') WITHIN GROUP (ORDER BY col2)) = 'B' then 1
else null
end) B,
count(case
when (LISTAGG(col2,'-') WITHIN GROUP (ORDER BY col2)) = 'C' then 1
else null
end) C,
count(case
when (LISTAGG(col2,'-') WITHIN GROUP (ORDER BY col2)) = 'D' then 1
else null
end) D
from T
GROUP BY col1
Thanks for your replies
感谢您的回复
回答by Gurwinder Singh
You can use window function rank()to achieve this.
您可以使用窗口函数rank()来实现这一点。
select col2, count(case when rn = 1 then 1 end) cnt from (
select t.*,
rank() over (partition by col1 order by case when col2 = 'A' then 1 else 2 end) rn
from table t
) group by col2;
回答by Marmite Bomber
The most general solution to your propositions where each key COL1is counted only in the first occurrence of the key COL2(in alphabetical order)
您的命题的最通用解决方案，其中每个键COL1仅在第一次出现时计算COL2（按字母顺序）
WITH tab AS
(
SELECT 'Z1' col1, 'A' col2 FROM dual UNION ALL
SELECT 'Z1' col1, 'B' col2 FROM dual UNION ALL
SELECT 'Z2' col1, 'A' col2 FROM dual UNION ALL
SELECT 'Z2' col1, 'C' col2 FROM dual UNION ALL
SELECT 'Z3' col1, 'A' col2 FROM dual UNION ALL
SELECT 'Z4' col1, 'D' col2 FROM dual
), tab2 as (
select COL1, COL2,
row_number() over (partition by COL1 order by COL2) as rn
from tab)
select COL1, COL2,
case when rn = 1 then 1 else 0 end is_valid
from tab2
order by 1,2
;
COL1 COL2 IS_VALID
---- ---- ----------
Z1 A 1
Z1 B 0
Z2 A 1
Z2 C 0
Z3 A 1
Z4 D 1
The rest is simple group by with a SUM on IS_VALID
其余的是简单的分组，在 IS_VALID 上使用 SUM
select COL2, sum(is_valid) cnt from tab3 -- TAB3 is the above row source
group by COL2
order by 1
COL2 CNT
---- ----------
A 3
B 0
C 0
D 1
回答by mathguy
select col2, count(case when col2 = col3 then 'x' end) as ct
from ( select col2, min(col2) over (partition by col1) as col3
from your_table
)
group by col2
order by col2 -- if needed
;
Explanation:
说明：
There is an inner query (a.k.a. "subquery") which returns one row for each row in the original table. It returns col2as is, and an additional (new) column, labeled col3. col3is calculated as the "first" or min()value of col2(in alphabetical order) for all the rows in the original table that have the same value in col1as the current row does. This is a typical example of an analytic function; partition by col1is similar to group by col1but it returns all the rows in the group (all the original rows from the original table) instead of one row per group, as would an aggregate function.
有一个内部查询（又名“子查询”），它为原始表中的每一行返回一行。它col2按原样返回，并返回一个附加的（新）列，标记为col3。col3计算为原始表中与当前行具有相同值的所有行的“第一个”或min()值col2（按字母顺序）col1。这是解析函数的典型例子；partition by col1类似于group by col1但它返回组中的所有行（原始表中的所有原始行）而不是每组一行，就像聚合函数一样。
To see what the inner query does by itself, select it and run it in your favorite front-end. You may add col1to the select in the inner query - that will make what's going on in this query even clearer. You'll get the initial table, with one more column, col3, that shows the "min" col2for each value of col1. I didn't include col1in the subquery because I don't need it, but add it back to see what the subquery really does.
要查看内部查询本身做了什么，请选择它并在您最喜欢的前端运行它。您可以col1在内部查询中添加选择 - 这将使此查询中发生的事情更加清晰。您将获得初始表，其中还有一列 ，col3显示 的col2每个值的“最小值” col1。我没有包含col1在子查询中，因为我不需要它，但是将它添加回来以查看子查询的真正作用。
Then in the outer query I take the results from the inner query and I group by col2. For each col2I count just how many times it is equal to the "min" value of col2for the corresponding col1value. That's what the caseexpression does in the count()function; when col2is not equal to col3, then casereturns null(by default) so the expression - and therefore the row - is not counted.
然后在外部查询中，我从内部查询中获取结果并分组col2。对于每个col2我计算它等于col2相应col1值的“最小”值的次数。这就是case表达式在count()函数中的作用；whencol2不等于col3，则case返回null（默认情况下）因此表达式 - 因此行 - 不计算在内。
I should add that the query written this way assumes there are no duplicate (col1, col2)rows in the original table. If there are, then the inner subquery should select from a sub-subquery; line 3 of my code should be
我应该补充一点，以这种方式编写的查询假设(col1, col2)原始表中没有重复的行。如果有，则内部子查询应从子子查询中进行选择；我的代码的第 3 行应该是
from (select distinct col1, col2 from your_table)
回答by Pham X. Bach
Assume your table name is table_name, One way to do it is using this:
假设你的表名是table_name，一种方法是使用这个：
WITH table_a AS
(
SELECT DISTINCT col1
FROM table_name
WHERE col2 = 'A'
)
SELECT col2,
SUM(CASE WHEN col1 IN (SELECT col1 FROM table_a)
THEN DECODE(col2, 'A', 1, 0)
ELSE 1 END
) count
FROM table_name
GROUP BY col2
ORDER BY col2;
Tested ok:
测试正常：
WITH table_name AS
(
SELECT 'Z1' col1, 'A' col2 FROM dual UNION ALL
SELECT 'Z1' col1, 'B' col2 FROM dual UNION ALL
SELECT 'Z2' col1, 'A' col2 FROM dual UNION ALL
SELECT 'Z2' col1, 'C' col2 FROM dual UNION ALL
SELECT 'Z3' col1, 'A' col2 FROM dual UNION ALL
--SELECT 'Z4' col1, 'B' col2 FROM dual UNION ALL
SELECT 'Z4' col1, 'D' col2 FROM dual
)
, table_a AS
(
SELECT DISTINCT col1
FROM table_name
WHERE col2 = 'A'
)
SELECT col2,
SUM(CASE WHEN col1 IN (SELECT col1 FROM table_a)
THEN DECODE(col2, 'A', 1, 0)
ELSE 1 END
) count
FROM table_name
GROUP BY col2
ORDER BY col2;
回答by Thorsten Kettner
You want to count each record where either col2 is 'A' or no 'A' record exists for col1.
您想计算 col2 为“A”或 col1 不存在“A”记录的每条记录。
select
col2,
count(
case
when col2 = 'A' or col1 not in (select col1 from table_name where col2 = 'A') then 1
end) as cnt
from table_name
group by col2;
回答by Mathavan AR
Use the below script:
使用以下脚本：
SELECT A.COL2, NVL(B.CNT, 0) AS CNT
FROM (SELECT DISTINCT COL2 FROM TET) A
LEFT JOIN (SELECT COL2, COUNT(COL2) AS CNT
FROM (SELECT SUBSTR(F, 1, INSTR(F, ',') - 1) AS COL2,
ROW_NUMBER() OVER(PARTITION BY SUBSTR(F, 1, INSTR(F, ',') - 1) ORDER BY SUBSTR(F, 1, INSTR(F, ',') - 1)) AS U
FROM (SELECT COL1,
LISTAGG(COL2, ',') WITHIN GROUP(ORDER BY COL2) || ',' AS F
FROM TET
GROUP BY COL1)) A
GROUP BY COL2) B
ON A.COL2 = B.COL2
ORDER BY A.COL2;
