时间戳

首页 > 解决方案 > 如何编写这些复杂的 MySQL 查询？

mysql - 如何编写这些复杂的 MySQL 查询？

问题描述

我有以下 2 个表：

表“船长”

ID 姓名
1 队长1
2 队长2
3 队长3
4 队长4
5 队长5
6 队长6
7 队长7
8 队长8
9 队长9
10 船长10

表“远征”

ID 数字 id_captain id_navire id_hero
1 1 1 10 8
2 2 2 1 5
3 3 1 8 3
4 4 10 9 6
5 5 5 7 4
6 6 6 5 4
7 7 7 3 7
8 8 8 2 8
9 9 9 1 3
10 10 1 4 2
11 11 6 3 1
12 12 8 6 1
13 13 5 8 6
14 14 4 9 9
15 15 3 10 4
16 16 10 2 2
17 17 9 3 3
18 18 8 7 7
19 19 9 8 10
20 20 7 2 2

我有这个查询：

指导最多探险的船长或船长在 SQL 中这样描述：

select id_captain, count(expedition.id) as expedition_count
  from expedition
  group by id_captain
  having expedition_count = max(expedition_count);

但没有成功。我期望结果是这样的：

姓名 expedition_count
队长1 3
队长9 3
队长8 3

标签： mysqlsqlcountmax

解决方案

这是几个步骤：获取每个字幕的计数，获取最大计数，仅显示最大计数的船长。

一种典型的方法是使用窗口函数（自 MySQL 8 起可用）：

select id_captain, expedition_count
from
(
  select 
    id_captain,
    count(*) as expedition_count,
    max(count(*)) over () as max(expedition_count)
  from expedition
  group by id_captain
) analyzed
where expedition_count = max_expedition_count;

或子查询：

select id_captain, count(*) as expedition_count
from expedition
group by id_captain
having expedition_count = 
(
  select count(*)
  from expedition
  group by id_captain
  order by count(*) desc
  limit 1
);

推荐阅读