mysql 选择最高级别数最多的部门名称

dced5bon 于 2023-01-16 发布在 Mysql

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

我有3个表-student，department和exam_results。我在这里尝试做的是选择所有的部门名称/s具有最高的等级，例如6。
我尝试了以下查询，但在本例中，我们有2个6级的部门，但信息学部门有2个最大等级，而化学部门只有1个，我仍在检索化学部门名称，不应返回该名称，而应仅返回化学部门名称（同样，如果化学部门有2个最大等级，则应能够检索信息学和化学记录）。

SELECT department
FROM (SELECT d.department_name as department, count(e_r.grade) as cnt
      FROM exam_results e_r
      INNER JOIN students s ON e_r.student_id = s.student_id
      INNER JOIN department d ON s.department_id = d.department_id
      WHERE e_r.grade = 6
      GROUP BY d.department_name
     ) as ex;

同样，使用下面的查询，我能够完成检索部门名称和WHERE子句中给定的"n"级的计数，但由于某种原因，我无法完成我真正想要的。

SELECT department_name, max(cnt) as cnt
FROM (SELECT d.department_name as department_name, e_r.grade, count(e_r.grade) as cnt
      FROM exam_results e_r
      INNER JOIN students s ON e_r.student_id = s.student_id
      INNER JOIN department d ON s.department_id = d.department_id
      WHERE grade = 6
      GROUP BY d.department_name, e_r.grade
     ) AS ex
GROUP BY department_name;

提供一个dbfiddle链接，其中包含所解释的示例：https://dbfiddle.uk/siEjfNXP
将很高兴收到任何建议或提示如何实现这一点，谢谢!
下面是带有示例值的表（全部包含在dbfiddle链接中）：
学生桌：
| 学生标识|部门标识|
| - ------|- ------|
| 1个|1个|
| 第二章|1个|
| 三个|第二章|
| 四个|第二章|
| 五个|三个|
| 六个|三个|
| 七|四个|
| 八个|四个|
| 九|五个|
| 十个|五个|
部门表：
| 部门标识|部门名称|
| - ------|- ------|
| 1个|信息学|
| 第二章|生物学|
| 三个|物理学|
| 四个|地理学|
| 五个|化学|
检查结果表：
| 学生标识|坡度|
| - ------|- ------|
| 1个|六个|
| 第二章|六个|
| 三个|四个|
| 四个|四个|
| 五个|三个|
| 六个|三个|
| 七|第二章|
| 八个|第二章|
| 九|六个|
| 十个|五个|

mysql

来源：https://stackoverflow.com/questions/75127210/select-department-s-name-s-with-the-highest-number-of-highest-grades

2条答案

按热度按时间

a6b3iqyw1#

如果你想要一个沼泽灵活solitution如果最高排名不是6

WITH CTE as
  (SELECT 
  d.department_name as department_name,e_r.grade, count(e_r.grade) as cnt
    , DENSE_RANK() OVER (ORDER BY e_r.grade DESC ,count(e_r.grade) DESC) rk
FROM  exam_results e_r
      INNER JOIN students s ON e_r.student_id = s.student_id
      INNER JOIN department d ON s.department_id = d.department_id
  WHERE (d.department_name,e_r.grade) IN
(select d.department_name as department_name, MAX(e_r.grade) 
      from exam_results e_r
      INNER JOIN students s ON e_r.student_id = s.student_id
      INNER JOIN department d ON s.department_id = d.department_id     
      group by d.department_name)
group by d.department_name, e_r.grade)
SELECT department_name,grade,cnt FROM CTE WHERE rk = 1

| 部门名称|坡度|碳纳米管|
| - ------|- ------|- ------|
| 信息学|六个|第二章|
fiddle

赞(0）回复(0）举报 2023-01-16

jrcvhitl2#

如果您只需要一行（具有最高等级的部门），则可以按等级数降序排序，然后将行数限制为1。

SELECT d.department_name as department
FROM exam_results e_r
INNER JOIN students s ON e_r.student_id = s.student_id
INNER JOIN department d ON s.department_id = d.department_id
WHERE e_r.grade = 6
GROUP BY d.department_name
ORDER BY COUNT(e_r.grade) DESC
LIMIT 1

检查here演示。
如果允许多个max值，可以使用DENSE_RANK窗口函数为计数指定一个排名顺序，然后获取denserank = 1的所有记录。

SELECT department
FROM (SELECT d.department_name as department,
             DENSE_RANK() OVER(ORDER BY COUNT(e_r.grade) DESC) AS rn
      FROM exam_results e_r
      INNER JOIN students s ON e_r.student_id = s.student_id
      INNER JOIN department d ON s.department_id = d.department_id
      WHERE e_r.grade = 6
      GROUP BY d.department_name
     ) AS ex
WHERE rn = 1

查看here演示。

赞(0）回复(0）举报 2023-01-16

我来回答

mysql 选择最高级别数最多的部门名称

2条答案

相关问题

热门标签

最新问答