如何将select语句组合在不同的列中

nom7f22z  于 2021-06-21  发布在  Mysql
关注(0)|答案(4)|浏览(298)

这是我正在做的陈述。创建一个查询,按军衔类别(军官、士官或部队)显示男童子军或女童子军的数量以及男女童子军的总数。

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

这些都是我想结合的陈述,因为它看起来太长了,我相信有一种方法可以更容易地做到这一点
这是输出的样子,我不想改变它为多行或不同数量的列。输出在这里

wtzytmuj

wtzytmuj1#

将查询更改为:

SELECT
(Select count(Gender)  as 'FOScout' from stormtroopers_officer join st_officer_assign on stormtroopers_officer.STID=st_officer_assign.STID where Gender='Female') as 'FOScout',
(SELECT count(Gender)  as 'FNScout' from stormtroopers_nco join st_nco_assign on stormtroopers_nco.STID=st_nco_assign.STID where Gender='Female') as 'FNScout',
(SELECT count(Gender)  as 'FTScout' from stormtroopers_troop join st_troop_assign on stormtroopers_troop.STID=st_troop_assign.STID where Gender='Female') as 'FTScout',
(Select count(Gender)  as 'MOScout' from stormtroopers_officer join st_officer_assign on stormtroopers_officer.STID=st_officer_assign.STID where Gender='male') as 'MOScout',
(SELECT count(Gender)  as 'MNScout' from stormtroopers_nco join st_nco_assign on stormtroopers_nco.STID=st_nco_assign.STID where Gender='male') as 'MNScout',
(SELECT count(Gender)  as 'MTScout' from stormtroopers_troop join st_troop_assign on stormtroopers_troop.STID=st_troop_assign.STID where Gender='male') as 'MTScout',
(SELECT count(Gender)  as 'Total Female Scouts' from stormtroopers_troop WHERE Gender = 'Female') as 'Total Female Scouts',
(SELECT count(Gender)  as 'Total Male Scouts' from stormtroopers_troop WHERE Gender='Male') as 'Total Male Scouts',
(SELECT count(Gender)  as 'Total Male Scouts' from stormtroopers_troop) as 'Total Male Scouts'

我在下面的帖子中回答了这个问题:如何在一个表中包含多个select语句
请把它标为答案,如果是这样的话。

2hh7jdfx

2hh7jdfx2#

可以声明变量并将每个计数赋给变量。最后选择所有指定的变量。

DECLARE @Countofficer INT;
   DECLARE @Countnco INT;
   SET @Countofficer =(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')

    SET @Countnco =(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')

    SELECT @Countofficer AS stormtroopers_officer ,@Countnco AS stormtroopers_nco
gorkyyrv

gorkyyrv3#

你可以用 GROUP BY ,如果可以将结果作为单独的行使用:

Select Gender, a.role, count(Gender)
from stormtroopers_officer o join
     st_officer_assign a
     on o.STID = a.STID
group by Gender, a.Role;
nwo49xxi

nwo49xxi4#

我想这会满足你的要求:

select FOScouts, FNScouts, FTScouts, MOScouts, MNScouts, MTScouts,
FOScouts+FNScouts+FTScouts as `Total Female Scouts`,
MOScouts+MNScouts+MTScouts as `Total Male Scouts`,
FOScouts+FNScouts+FTScouts+MOScouts+MNScouts+MTScouts as `Total Scouts`
from (select count(case when gender='Female' then 1 end) as FOScouts,
      count(case when gender='Male' then 1 end) as MOScouts
      from stormtroopers_officer st_o
      join st_officer_assign st_o_a 
      on st_o_a.stid = st_o.stid
      where st_o_a.role = 'Scout') o
     join
     (select count(case when gender='Female' then 1 end) as FNScouts,
      count(case when gender='Male' then 1 end) as MNScouts
      from stormtroopers_nco st_n
      join st_nco_assign st_n_a 
      on st_n_a.stid = st_n.stid
      where st_n_a.role = 'Scout') n
     join
     (select count(case when gender='Female' then 1 end) as FTScouts,
      count(case when gender='Male' then 1 end) as MTScouts
      from stormtroopers_troop st_t
      join st_troop_assign st_t_a on st_t_a.stid = st_t.stid
      where st_t_a.role = 'Scout') t

相关问题