oracle 使用单个 PL SQL 查询返回多个计数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/767915/
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
提示：将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间：2020-09-18 18:06:06 来源:igfitidea点击:
Return multiple counts with single PL SQL Query
提问by Preets
I have the following two PL/SQL Oracle Queries that return a count:
我有以下两个返回计数的 PL/SQL Oracle 查询：
SELECT count(*)
INTO counter_for_x
FROM Table_Name
WHERE Column_Name = 'X';
SELECT count(*)
INTO counter_for_y
FROM Table_Name
WHERE Column_Name = 'Y';
Is it possible to write a single query that returns both the counts and populates the respective counter variables ?
是否可以编写一个查询来返回计数并填充相应的计数器变量？
回答by Quassnoi
SELECT (
SELECT COUNT(*)
FROM table_name
WHERE column_name = 'X'
),
(
SELECT COUNT(*)
FROM table_name
WHERE column_name = 'Y'
)
INTO counter_for_x, counter_for_y
FROM dual
回答by Erich Kitzmueller
You can, but I wouldn't do it...
你可以，但我不会这样做......
select nvl(sum(decode(column_name,'X',1,0)),0), nvl(sum(decode(column_name,'Y',1,0)),0)
into counter_for_x, counter_for_y
from table_name
where column_name in ('X', 'Y');
回答by Eoin Campbell
Another way.
其它的办法。
SELECT
Max(CASE WHEN Column_Name = 'X' THEN Count ELSE NULL END) AS Count_X ,
MAX(CASE WHEN Column_Name = 'Y' THEN Count ELSE NULL END) AS Count_Y
FROM
(
SELECT count(*) as Count, Column_Name
FROM Table_Name
Where Column_Name in ('X', 'Y')
Group By Column_Name
) AS InnerTable
回答by Tony Andrews
This will do it:
这将做到：
select count(decode(column_name,'X',1)) x_count
, count(decode(column_name,'Y',1)) y_count
into counter_for_x, counter_for_y
from table_name
where column_name in ('X','Y');
or if you prefer using CASE:
或者如果您更喜欢使用 CASE：
select count(case when column_name = 'X' then 1 end) x_count
, count(case when column_name = 'Y' then 1 end) y_count
into counter_for_x, counter_for_y
from table_name
where column_name in ('X','Y');
