基于足球场地坐标数据库，查询进球次数最多的场地坐标

嘿，针对你的需求，我整理了几种不同场景下的SQL查询方案，适配常见的数据库类型，你可以根据自己用的数据库来选：

基础方案（适用于单条结果或只取第一条） #

这个方案适合你只需要获取进球次数最多的一组坐标（即使有并列也只取第一个）：

MySQL/MariaDB #

SELECT l.X_C, l.Y_C, COUNT(g.locID) AS goal_count
FROM location l
INNER JOIN goals g ON l.locID = g.locID
GROUP BY l.X_C, l.Y_C
ORDER BY goal_count DESC
LIMIT 1;

SQL Server #

SELECT TOP 1 l.X_C, l.Y_C, COUNT(g.locID) AS goal_count
FROM location l
INNER JOIN goals g ON l.locID = g.locID
GROUP BY l.X_C, l.Y_C
ORDER BY goal_count DESC;

PostgreSQL/Oracle 12+ #

SELECT l.X_C, l.Y_C, COUNT(g.locID) AS goal_count
FROM location l
INNER JOIN goals g ON l.locID = g.locID
GROUP BY l.X_C, l.Y_C
ORDER BY goal_count DESC
FETCH FIRST 1 ROW ONLY;

进阶方案（获取所有并列进球最多的坐标） #

如果存在多个坐标进球次数相同且都是最高的，上面的基础方案只会返回其中一个，用窗口函数可以把所有并列第一的都查出来：

WITH goal_stats AS (
    SELECT 
        l.X_C, 
        l.Y_C, 
        COUNT(g.locID) AS goal_count,
        RANK() OVER (ORDER BY COUNT(g.locID) DESC) AS rank_num
    FROM location l
    INNER JOIN goals g ON l.locID = g.locID
    GROUP BY l.X_C, l.Y_C
)
SELECT X_C, Y_C, goal_count
FROM goal_stats
WHERE rank_num = 1;

这个方案几乎适配所有支持窗口函数的数据库（比如MySQL 8+、SQL Server 2008+、PostgreSQL、Oracle等）。

小提示 #

• 用INNER JOIN是因为我们只关心有进球记录的坐标，如果你的location表存在没有进球的坐标，这些会被自动过滤掉，符合需求。
• 统计次数时用COUNT(g.locID)比COUNT(*)更严谨，因为它只会统计goals表中有效的关联记录（避免NULL值干扰）。

内容的提问来源于stack exchange，提问作者user9670742