这是我正在做的陈述。创建一个查询,按军衔类别(军官、士官或部队)显示男童子军或女童子军的数量以及男女童子军的总数。
SELECT
(Select count(Gender)
from stormtroopers_officer
join st_officer_assign on stormtroopers_officer.STID=st_officer_assign.STID
where Gender='Female' AND st_officer_assign.Role = 'Scout') as 'FOScout',
(SELECT count(Gender)
from stormtroopers_nco
join st_nco_assign on stormtroopers_nco.STID=st_nco_assign.STID
where Gender='Female' AND st_nco_assign.Role = 'Scout') as 'FNScout',
(SELECT count(Gender)
from stormtroopers_troop
join st_troop_assign on stormtroopers_troop.STID=st_troop_assign.STID
where Gender='Female' AND st_troop_assign.Role = 'Scout') as 'FTScout',
(Select count(Gender)
from stormtroopers_officer
join st_officer_assign on stormtroopers_officer.STID=st_officer_assign.STID
where Gender='male' AND st_officer_assign.Role = 'Scout') as 'MOScout',
(SELECT count(Gender)
from stormtroopers_nco
join st_nco_assign on stormtroopers_nco.STID=st_nco_assign.STID
where Gender='male' AND st_nco_assign.Role = 'Scout') as 'MNScout',
(SELECT count(Gender)
from stormtroopers_troop
join st_troop_assign on stormtroopers_troop.STID=st_troop_assign.STID
where Gender='male' AND st_troop_assign.Role = 'Scout') as 'MTScout',
(SELECT count(Gender)
from stormtroopers_officer
WHERE Gender = 'Female') as 'Total Female Scouts',
(SELECT count(Gender)
from stormtroopers_troop
WHERE Gender='Male') as 'Total Male Scouts',
(SELECT count(Gender)
from stormtroopers_troop) as 'Total Male Scouts';
这些都是我想结合的陈述,因为它看起来太长了,我相信有一种方法可以更容易地做到这一点
这是输出的样子,我不想改变它为多行或不同数量的列。输出在这里
4条答案
按热度按时间wtzytmuj1#
将查询更改为:
我在下面的帖子中回答了这个问题:如何在一个表中包含多个select语句
请把它标为答案,如果是这样的话。
2hh7jdfx2#
可以声明变量并将每个计数赋给变量。最后选择所有指定的变量。
gorkyyrv3#
你可以用
GROUP BY
,如果可以将结果作为单独的行使用:nwo49xxi4#
我想这会满足你的要求: