SQL：如果存在 - >比较两个表并检查它们是否具有相同的行数

Question

我处于我的SQL知识的末尾。我有问题，我想检查两个表是否具有相同的行数。

一个例子可能是我想向所有学校展示椅子数量相同的学生人数。我的伪代码如下所示：

SELECT * FROM schools WHERE 
    ((SELECT COUNT(*) FROM students) = (SELECT COUNT(*) FROM chairs)); 

或用伪计算：students - chairs = 0;

SELECT * FROM schools WHERE 
    ((SELECT COUNT(*) FROM students) - (SELECT COUNT(*) FROM chairs) = 0); 

这样的结构与SQL可能，如果是这样如何？

Answer 1

你的做法似乎有点非正统的，但你可以用相关子查询做到这一点：

SELECT s.* 
FROM schools s. 
WHERE ((SELECT COUNT(*) FROM students st WHERE st.school_id = s.school_id) = 
     (SELECT COUNT(*) FROM chairs c WHERE c.school_id = s.school_id) 
    ); 

我会更倾向于做聚合后加入：

select st.school_id 
from (SELECT st.school_id, COUNT(*) as cnt 
     FROM students st 
     GROUP BY st.school_id 
    ) st JOIN 
    (SELECT c.school_id, COUNT(*) as cnt 
     FROM chairs c 
     GROUP BY c.school_id 
    ) c 
    ON st.school_id = c.school_id AND st.cnt = c.cnt; 

的两个版本微妙的不同。此版本只会返回至少有一名学生（和主席）的学校ID。

编辑：

我要指出的是，如果你只是想知道，如果两个表具有相同的行数，你可以返回在SELECT一个布尔值：由于存在

SELECT ((SELECT COUNT(*) FROM chairs c) = 
     (SELECT COUNT(*) FROM students st) 
     ) as num_rows_same_flag 

尽管如此，我认为你想让每个学校的数字相同。