使用SELECT UNION并从一个表返回两个列的输出

Question

我正在创建一个查询，统计我的表中男女演员的数量。我目前的发言如下：

Select COUNT(ActorGender) “Male Actors” 
from (tblActor ta WHERE ta.ActorGender in(‘m’)
UNION
Select COUNT(ActorGender) “Female Actors” 
from tblActor ta 
WHERE ta.ActorGender in(‘f’);

最后的输出是：

Male Actors
-----------
          7
         21

我希望输出看起来像：

Male Actors   Female Actors
-----------   -------------
          7              21

我正在寻找一种替代方法来处理这个，而不使用子句的情况。

像往常一样提前感谢你的帮助。

Answer 1

另一种不用用例的方法：

select sum(males) as "Male Actors", sum(females) as "Female Actors" 
from 
(select count(actorGender) as Males, 0 as Females
from tblActor 
where actorGender = 'm'
union all
select 0 as males, count(actorGender) as Females
from tblActor
where actorGender = 'f')

应导致

Male Actors    Female Actors
-----------    -------------
7              21 

Answer 2

另一种方法(没有CASE表达式)：

SELECT 
  ( SELECT COUNT(*)
    FROM tblActor 
    WHERE ActorGender = 'm' 
  ) AS MaleActors
, ( SELECT COUNT(*)
    FROM tblActor 
    WHERE ActorGender = 'f' 
  ) AS FemaleActors
FROM 
    dual ;

以及使用CROSS连接的更多解决方案：

SELECT m.MaleActors, f.FemaleActors
FROM 
  ( SELECT COUNT(*) AS MaleActors
    FROM tblActor 
    WHERE ActorGender = 'm' 
  ) m
  CROSS JOIN
  ( SELECT COUNT(*) AS FemaleActors
    FROM tblActor 
    WHERE ActorGender = 'f' 
  ) f  ;

Answer 3

这样做可以：

SELECT  COUNT(CASE WHEN ActorGender = 'm' THEN 1 ELSE NULL END) MaleActors,
        COUNT(CASE WHEN ActorGender = 'f' THEN 1 ELSE NULL END) FemaleActors
FROM tblActor 
WHERE ActorGender IN ('m','f')