关联含重复gid的表时，如何保持左表行数并输出唯一gid结果

问题背景 #

现有两张表：

table1（费用表） #

id gid   cost
1  1000  123 
2  2000  123 
3  1000  111 
4  3000  222
5  1000  333

table2（名称表，现支持单gid对应多name） #

id gid   name      feature
1  1000  AAA       xxx
2  2000  BBB  
3  3000  CCC
4  4000  DDD
5  2000  NewName   yyy

需要输出唯一gid，对应table1的总费用totalCost；若table2中同一gid绑定多个name，仅保留其中一个，期望结果：

gid  name    totalCost
1000 AAA     567  -- 123+111+333=567
2000 BBB     123
3000 CCC     222

解决方案 #

方法1：窗口函数筛选指定优先级的name #

通过ROW_NUMBER()给每个gid下的记录排序，仅保留排序后的第一条（可自定义排序规则，比如保留最早录入的name）：

WITH table2_unique AS (
    SELECT gid, name,
           ROW_NUMBER() OVER (PARTITION BY gid ORDER BY id) AS rn  -- 按table2的id排序，保留最早的name
    FROM table2
)
SELECT a.gid, b.name, a.totalCost
FROM (
    SELECT gid, SUM(cost) AS totalCost
    FROM table1
    GROUP BY gid
) a
LEFT JOIN (
    SELECT gid, name FROM table2_unique WHERE rn = 1
) b ON a.gid = b.gid;

若想保留字典序最小的name，将ORDER BY id改为ORDER BY name即可。

方法2：聚合函数取任意唯一name #

如果不需要指定保留哪一个name，仅需保证唯一，可直接用MIN()/MAX()聚合table2的name：

SELECT a.gid, b.name, a.totalCost
FROM (
    SELECT gid, SUM(cost) AS totalCost
    FROM table1
    GROUP BY gid
) a
LEFT JOIN (
    SELECT gid, MIN(name) AS name  -- 取字典序最小的name，用MAX则取最大的
    FROM table2
    GROUP BY gid
) b ON a.gid = b.gid;

方法3：子查询直接取单条name（简洁写法） #

在关联时通过子查询直接获取每个gid的一条name记录，注意不同数据库的语法差异：

-- MySQL/PostgreSQL 写法
SELECT a.gid, 
       (SELECT name FROM table2 WHERE gid = a.gid LIMIT 1) AS name,
       a.totalCost
FROM (
    SELECT gid, SUM(cost) AS totalCost
    FROM table1
    GROUP BY gid
) a;

-- SQL Server 写法（替换LIMIT 1为TOP 1）
SELECT a.gid, 
       (SELECT TOP 1 name FROM table2 WHERE gid = a.gid) AS name,
       a.totalCost
FROM (
    SELECT gid, SUM(cost) AS totalCost
    FROM table1
    GROUP BY gid
) a;

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