SQL 使用 SELECT UNION 并从一张表中返回两列的输出
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13592499/
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
Using SELECT UNION and returning output of two columns from one table
提问by Taryn
I am creating a query that counts the amount of male and female actors in my table. My current statement is as such:
我正在创建一个查询来计算我的表中男女演员的数量。我目前的声明是这样的：
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');
The output ends up being:
输出最终是：
Male Actors
-----------
7
21
I want the output to look like:
我希望输出看起来像：
Male Actors Female Actors
----------- -------------
7 21
I am looking for an alternative to go about this without using the CASE WHEN or THEN clauses.
我正在寻找不使用 CASE WHEN 或 THEN 子句的替代方法。
Thanks in advance for the help as usual.
像往常一样提前感谢您的帮助。
回答by Lamak
This would do:
这会做：
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')
回答by ypercube??
Another way (without CASEexpression):
另一种方式（没有CASE表达）：
SELECT
( SELECT COUNT(*)
FROM tblActor
WHERE ActorGender = 'm'
) AS MaleActors
, ( SELECT COUNT(*)
FROM tblActor
WHERE ActorGender = 'f'
) AS FemaleActors
FROM
dual ;
and more solution with CROSSjoin:
以及更多连接解决方案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 ;
回答by Hellion
another way without using case:
另一种不使用案例的方式：
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')
should result in
应该导致
Male Actors Female Actors
----------- -------------
7 21
回答by Taryn
If you are using Oracle 11g+, then you can use PIVOT:
如果您使用的是 Oracle 11g+，那么您可以使用PIVOT：
select *
from
(
select actorgender
from tblActor
) src
pivot
(
count(actorgender)
for actorgender in ('m' MaleActors, 'f' FemaleActors)
) piv
The result would be:
结果将是：
| MALEACTORS | FEMALEACTORS |
-----------------------------
| 4 | 5 |
Or you can use a CROSS JOINto get the same result:
或者您可以使用 aCROSS JOIN来获得相同的结果：
select m.MaleActors, f.FemaleActors
from
(
select count(ActorGender) MaleActors, 'm' Gender
from tblActor
where ActorGender = 'm'
) m
cross join
(
select count(ActorGender) FemaleActors, 'f' Gender
from tblActor
where ActorGender = 'f'
) f
