需要在mysql中编写一个查询来查找以下内容

wwwo4jvm 于 2021-07-26 发布在 Java

关注(0)|答案(2)|浏览(259)

我有一张结构如下的表

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);

不会有结果。
感谢您对以上项目的帮助

sql mysql

来源：https://stackoverflow.com/questions/62389932/need-to-write-a-query-in-mysql-to-find-the-following

2条答案

按热度按时间

nwlls2ji1#

我会使用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;

我认为你误解了这个问题。它寻找的是一个结果集，每行有两个教室，而不是一个有计数的教室。

赞(0）回复(0）举报 2021-07-26

ztyzrc3y2#

干得好：

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

赞(0）回复(0）举报 2021-07-26

我来回答

需要在mysql中编写一个查询来查找以下内容

2条答案

相关问题

热门标签

最新问答