SQL关联mobile_payments表后action_id重复，Group By、DISTINCT无效如何解决？

你遇到的重复数据问题是因为customer.mobile_payments表和customer.actions表是一对多关系：同一个customer_id在mobile_payments表中存在多条支付记录，关联后每条action记录会和该客户的所有支付记录做笛卡尔积拼接，所以原本500条的action数据会膨胀到4000条。
之前用DISTINCT或者GROUP BY无效，是因为同一个action_id关联的多条支付记录的payment_type值不同，聚合时无法自动合并。

以下方案均可以实现每个action_id仅返回一条结果，同时保留所有满足过滤条件的actions记录： #

方案1：窗口函数取单条支付记录（支持MySQL8.0+、PostgreSQL、SQL Server等主流数据库） #

如果需要按业务规则取每个客户的某一条支付记录（比如最新的支付记录对应的payment_type），可以先对mobile_payments表按customer_id分组排序，每个组仅取第一条后再关联：

select a.action_id, a.customer_id, a.request_date, mp.payment_type 
from customer.actions a
inner join customer.customers c on c.id = a.customer_id
inner join customer.entities e on e.id = c.entity_id
-- 预处理支付表，每个customer_id只取1条记录
left join (
    select 
        customer_id, 
        payment_type,
        -- 按支付时间倒序取最新的一条，无支付时间字段可以替换为其他排序规则，无需规则随便取可以写order by 1
        row_number() over(partition by customer_id order by payment_time desc) as rn 
    from customer.mobile_payments
) mp on mp.customer_id = a.customer_id and mp.rn = 1
where a.request_url like '%/test%'
order by a.request_date desc;

注意：这里用left join是为了保留没有支付记录的action数据，如果仅需要返回有支付记录的action，可以把left join改回inner join。

方案2：老版本MySQL兼容方案（不支持窗口函数的场景） #

如果使用MySQL5.x及以下不支持窗口函数的版本，可以先对支付表按customer_id做聚合，每个客户仅取一个payment_type：

select a.action_id, a.customer_id, a.request_date, mp.payment_type 
from customer.actions a
inner join customer.customers c on c.id = a.customer_id
inner join customer.entities e on e.id = c.entity_id
left join (
    select customer_id, max(payment_type) as payment_type -- 无需规则随便取可以用max/min，要取最新的可以关联max(payment_time)过滤对应记录
    from customer.mobile_payments
    group by customer_id
) mp on mp.customer_id = a.customer_id
where a.request_url like '%/test%'
order by a.request_date desc;

方案3：子查询直接取值（简单场景最便捷） #

如果仅需要获取payment_type一个字段，可以直接在select子句中写子查询取值：

select 
    a.action_id, 
    a.customer_id, 
    a.request_date,
    (select payment_type from customer.mobile_payments where customer_id = a.customer_id limit 1) as payment_type
from customer.actions a
inner join customer.customers c on c.id = a.customer_id
inner join customer.entities e on e.id = c.entity_id
where a.request_url like '%/test%'
order by a.request_date desc;

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