SQL多表JOIN后GROUP BY查询出现重复数据如何去除？

问题背景 #

• 现有3张业务数据表，结构参考ERD图示：
  
• 业务需求：统计各product_id对应的订单计数，按计数值倒序排列，同时根据favourites表中用户对产品的收藏关系生成isFavorite字段，期望返回的JSON结构如下：

{
    "id": 2,
    "title": "30 % discount on pizza.",
    "price": 100,
    "count": 4,
    "isFavorite": false
},

• 初始编写的SQL查询执行后返回重复结果，重复效果参考下图：
  

初始SQL语句如下：

SELECT PRODUCTS.ID AS "Products__id",
    PRODUCTS.TITLE AS "Products__title",
    PRODUCTS.PRICE AS "Products__price",
    (COUNT(INVOICES.PRODUCT_ID)) AS "count",
    CASE
                    WHEN FAVORITES.USER_ID IS NULL THEN FALSE
                    ELSE TRUE
    END AS "isFavorite"
FROM PRODUCTS PRODUCTS
LEFT JOIN FAVORITES FAVORITES ON FAVORITES.PRODUCT_ID = PRODUCTS.ID
INNER JOIN INVOICES INVOICES ON PRODUCTS.ID = INVOICES.PRODUCT_ID
GROUP BY PRODUCTS.ID,
    FAVORITES.USER_ID
ORDER BY COUNT DESC
LIMIT 100

问题根因 #

重复结果由两个写法问题导致：

• 直接左连接favourites表时，单款产品若被多名用户收藏，会生成多条对应不同user_id的关联行，后续关联订单表会产生笛卡尔积，放大数据行数
• 分组字段加入了FAVORITES.USER_ID，会将同一款产品按不同收藏用户拆分为多条独立记录，同时关联后的COUNT统计会被收藏表的冗余行干扰，出现计数不准的问题

修复方案 #

避免直接关联收藏表参与分组和计数，先单独聚合订单维度的统计值，再通过EXISTS判断收藏状态，从根源上杜绝关联产生的重复行。
如果需要查询指定登录用户视角下的收藏状态，将SQL中的:current_user_id替换为实际用户ID即可，修正后的SQL如下：

SELECT 
    p.ID AS id,
    p.TITLE AS title,
    p.PRICE AS price,
    inv.count AS count,
    CASE WHEN EXISTS(
        SELECT 1 FROM FAVORITES f 
        WHERE f.PRODUCT_ID = p.ID AND f.USER_ID = :current_user_id
    ) THEN TRUE ELSE FALSE END AS isFavorite
FROM PRODUCTS p
INNER JOIN (
    SELECT PRODUCT_ID, COUNT(*) AS count
    FROM INVOICES
    GROUP BY PRODUCT_ID
) inv ON p.ID = inv.PRODUCT_ID
ORDER BY inv.count DESC
LIMIT 100

如果业务需求仅判断产品是否被任意用户收藏，不需要区分当前用户，删除EXISTS子句中的AND f.USER_ID = :current_user_id条件即可。

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