我正在为一个班级解决这个SQL问题.
请计算一下男生的人数和人数 女性学生的比例.学生是指已注册的人. 唱片.
预期结果应该如下所示
然而，我一直无法得到以这种格式返回的结果，在这一点上我被困在这里
利用这个代码
SELECT
COUNT(Distinct enrollment.personID) as male
FROM dbo.enrollment
RIGHT JOIN dbo.person ON person.personID=enrollment.personID
WHERE person.gender='male'
UNION
SELECT
COUNT(Distinct enrollment.personID) as female
FROM dbo.enrollment
RIGHT JOIN dbo.person ON person.personID=enrollment.personID
WHERE person.gender='female';
任何帮助我指向正确格式的人都将不胜感激.
实际上使用UNION运行两个单独的查询来获得male和female计数.
要获得所需内容，您必须在一个查询中使用conditional aggregation和CASE语句.这可以更高效，并减少对两个单独查询的需要:
SELECT
COUNT(DISTINCT CASE WHEN person.gender = 'male' THEN enrollment.personID END) as male,
COUNT(DISTINCT CASE WHEN person.gender = 'female' THEN enrollment.personID END) as female
FROM dbo.enrollment
RIGHT JOIN dbo.person ON person.personID = enrollment.personID;
用subquery解决这个问题的另一个更易读、更简单的方法是:
SELECT SUM(p.gender = 'male') AS male, SUM(p.gender = 'female') AS female
FROM person AS p
WHERE p.personID IN (SELECT personID FROM enrollment);