我有一个结构如下所示的表
CREATE TABLE IF NOT EXISTS list (
`LastName` VARCHAR(13) CHARACTER SET utf8,
`FirstName` VARCHAR(14) CHARACTER SET utf8,
`Grade` INT,
`Classroom` INT
);
这里是我试图找到的--找到所有成对的教室，里面有相同数量的学生。 每对只报告一次。 报告教室和学生人数。
我尝试了以下脚本
select classroom,count(*) as cnt
from list
group by classroom
having count(*) in (select c.cnt as v
from
(select classroom,count(*) as cnt
from list
group by classroom)c
group by c.cnt
having count(classroom) =2);
但没有结果。
感谢您对上述项目的任何帮助
我会使用CTES:
with c as (
select classroom, count(*) as cnt
from list
group by classroom
)
select c1.classroom, c2.classroom
from c c1 join
c c2
on c1.cnt = c2.cnt and c1.classroom < c2.classroom;
我认为你误解了这个问题。 它寻找的是每行有两个教室的结果集，而不是一个有计数的教室。
给你:
select a.classroom, b.classroom, a.cnt
from (
select classroom, count(*) as cnt from list group by classroom
) a
join (
select classroom, count(*) as cnt from list group by classroom
) b on a.cnt = b.cnt and a.classroom < b.classroom
