代码之家 › 专栏 › 技术社区 › Palak Jadav

使用join获取多个表的数据计数

join sql-server sql

Palak Jadav · 技术社区 · 6 年前

我有以下表格:

    1. school
       - id
       - name
    2. grade
       - id
       - name
       -school_id
    3. class
       - id
       - name
       - grade_id
   4. student
       - id
       - name
       - class_id
   5. donation
       - id
       - amount
       - student_id

我想得到每个学校的年级,班级,学生,捐款我试过这个问题

    SELECT school.id AS ID, school.name AS Name,COUNT(student.id) AS 
    Students,COUNT(class.id) AS Class,COUNT(grade.id) AS Grade
    FROM (((
    INNER JOIN class ON student.classId=class.id )
    INNER JOIN grade ON class.gradeId=grade.id)
    INNER JOIN school ON grade.sclId=school.id)
    GROUP BY ID;

但它返回了错误的结果。花足够的时间来解决这个问题,但没有任何解决办法。有人能帮忙吗?

3 回复 | 直到 6 年前

Zafor 6 年前

您可以尝试以下查询

SELECT sc.Id, sc.Name
    , SUM(CASE WHEN g.Id IS NOT NULL THEN 1 ELSE 0 END ) AS Grade
    , SUM(CASE WHEN c.Id IS NOT NULL THEN 1 ELSE 0 END ) AS Class
    , SUM(CASE WHEN s.Id IS NOT NULL THEN 1 ELSE 0 END ) AS Students
FROM School AS sc
    LEFT OUTER JOIN Grade AS g ON sc.Id = g.sclId
    LEFT OUTER JOIN Class AS c ON g.Id = c.GradeId
    LEFT OUTER JOIN Student AS s ON c.Id = s.classId
GROUP BY sc.Id, sc.Name

Barbaros Özhan 6 年前

您可以尝试不带这些括号的以下查询。你还需要包括 non-aggregated 柱 sc.name 在 group by 列表。顺便考虑一下 LEFT JOIN 即使学生至少有其他三个表中的一个表的数据不匹配:

SELECT sc.id AS ID,
       sc.name AS Name,
       COUNT(st.id) AS Students,
       COUNT(c.id) AS Class,
       COUNT(g.id) AS Grade
  FROM student s
  LEFT JOIN class c ON c.id = s.classId 
  LEFT JOIN grade g ON g.id = c.gradeId
  LEFT JOIN school sc ON sc.id = g.sclId
 GROUP BY sc.id, sc.name;

Gordon Linoff 6 年前

您有沿着多个维度的连接,因此行将被这些连接相乘。计数有两种方法。更“正确”的方法是在进行联接之前进行聚合。

第二种方法比较简单,所以我建议 COUNT(DISTINCT) :

SELECT sc.id AS ID, sc.name AS Name,
       COUNT(DISTINCT s.id) AS num_Students,
       COUNT(DISTINCT c.id) AS num_Classes,
       COUNT(DISTINCT g.id) AS num_Grades
FROM student s INNER JOIN
     class c
     ON s.classId = c.id INNER JOIN
     grade g
     ON c.gradeId = g.id INNER JOIN
     school sc
     ON g.sclId = s.id
GROUP BY sc.id, sc.name;

笔记:

“真实”SQL数据库中的联接不需要括号。
您应该按所有非聚合列(包括 name 以及 id ).
强烈建议使用表别名。