sql - 分组依据不显示所有行

Question

我有一个表 tblPersonaldata 和 tblStudentsadmitted

tblPersonalData

UID  Name Gender
------------------------ 
 E1   xyz   M
 E2   pqr   M
 E3   mno   M

tblStudentsadmitted

UID   Status  Stage
----------------------
E1     Y        1
E2     Y        2
E3     Y        1

现在我想要这样的数据:

Gender  Stage1   Stage2
 M        2       1

但在这种情况下，我没有获得女性性别的数据。我想要女性性别的数据，即使它为空

我试过这个:

select 
    case 
        when gender='M' then 'Male' 
        when gender='F' then 'Female' 
    end as Gender,
    sum(case when Stage=1 then 1 else 0) end as Stage1,
    sum(case when Stage=2 then 1 else 0) end as Stage2
from  tblPersonaldata A inner join 
      tblStudentsadmitted B on A.UID=B.UID 
where B.Status='Y'
group by Gender

Answer 1

<罢工>

<罢工>

SELECT  CASE WHEN a.Gender = 'M' THEN 'Male' ELSE 'FEMALE' END Gender,
        SUM(CASE WHEN Stage = 1 THEN 1 ELSE 0 END) Stage1,
        SUM(CASE WHEN Stage = 2 THEN 1 ELSE 0 END) Stage2
FROM    personal a
        LEFT JOIN studentadmitted b
            ON a.UID = b.UID AND b.Status = 'Y'
GROUP   BY a.Gender

• SQLFiddle Demo

<罢工>

SELECT  CASE WHEN c.Gender = 'M' THEN 'Male' ELSE 'Female' END Gender,
        SUM(CASE WHEN Stage = 1 THEN 1 ELSE 0 END) Stage1,
        SUM(CASE WHEN Stage = 2 THEN 1 ELSE 0 END) Stage2
FROM    (SELECT 'F' Gender UNION SELECT 'M' Gender) c
        LEFT JOIN personal a
            ON a.Gender = c.Gender
        LEFT JOIN studentadmitted b
            ON a.UID = b.UID AND b.Status = 'Y'
GROUP   BY c.Gender

• SQLFiddle Demo

输出

╔════════╦════════╦════════╗
║ GENDER ║ STAGE1 ║ STAGE2 ║
╠════════╬════════╬════════╣
║ Female ║      0 ║      0 ║
║ Male   ║      2 ║      1 ║
╚════════╩════════╩════════╝