使用单个PL SQL查询返回多个计数

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 ?

是否可以编写一个返回计数并填充相应计数器变量的查询?

4 个解决方案

#1

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

#2

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');

#3

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

#4

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');

#1

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

#2