对多个表和联接使用groupby的sql计数

7tofc5zh  于 2021-08-09  发布在  Java
关注(0)|答案(3)|浏览(360)

我有3张table(学校、学生、活动),就像这样:
学校

学生

活动

我想按性别列出2020年每所学校的所有活动和参与者人数,如下表所示:

我做了一个这样的查询,但它没有按我想要的那样工作:

SELECT Sc.SchoolName, A.ActivityName, Sc.Year COUNT(Gender)
FROM School Sc
JOIN Student S ON Sc.SchoolID=S.SchoolID
JOIN Activity A ON S.ActivityID=A.ActivityID
GROUP BY Gender

我怎样才能解决这个问题?你能给我一个解决办法吗?

ig9co6j1

ig9co6j11#

您需要条件聚合:

SELECT 
    Sc.SchoolName, 
    A.ActivityName, 
    Sc.Year, 
    SUM(CASE WHEN S.Gender = 'F' THEN 1 ELSE 0 ENd) F,
    SUM(CASE WHEN S.Gender = 'M' THEN 1 ELSE 0 ENd) M
FROM School Sc
JOIN Student S  ON Sc.SchoolID  = S.SchoolID
JOIN Activity A ON S.ActivityID = A.ActivityID
GROUP BY 
    Sc.SchoolName, 
    A.ActivityName, 
    Sc.Year
btqmn9zl

btqmn9zl2#

SELECT 
    Sc.SchoolName, 
    A.ActivityName, 
    Sc.Year, 
    COUNT(CASE WHEN S.Gender = 'F' THEN StudentID END) as F,
    COUNT(CASE WHEN S.Gender = 'M' THEN StudentID END) as M
FROM School Sc
INNER JOIN Student S  ON Sc.SchoolID  = S.SchoolID
INNER JOIN Activity A ON S.ActivityID = A.ActivityID
WHERE YEAR LIKE '2020-%'
GROUP BY 
    Sc.SchoolName, 
    A.ActivityName, 
    Sc.Year
jrcvhitl

jrcvhitl3#

请使用下面的查询,

SELECT Sc.SchoolName, Sc.Year, A.ActivityName,
      case when Sc.Gender = 'M' then count(1) end as M,
      case when Sc.Gender = 'F' then count(1) end as F
FROM School Sc
JOIN Student S ON Sc.SchoolID=S.SchoolID
JOIN Activity A ON S.ActivityID=A.ActivityID
GROUP BY Sc.SchoolName, Sc.Year, A.ActivityName;

相关问题