如何将SQL Union结果按ID合并为单行数据

我现有SQL查询用于从多张表汇总数据，当前返回结果如下：

id  | name  |             email             | surname |        last_updated        |      phone    |  phone_no     | birthday   | order_count | email_col_count | review_track_count | loyalty_count 
-----+-------+-------------------------------+---------+----------------------------+---------------+---------------+------------+-------------+-----------------+--------------------+---------------
 232 |       | 8888@gmail.com                |         | 2023-04-02 20:05:53.186+00 |               |               | 1994/11/07 |           0 |               0 |                  0 |             1
 231 |       | 1234457@gmail.com             |         | 2023-04-02 20:01:17.629+00 |               |               | 1994/11/07 |           0 |               0 |                  0 |             1
 230 |       | 9999@gmail.com                |         | 2023-04-02 19:58:44.432+00 |               |               |            |           4 |               0 |                  0 |             0
 230 |       | 9999@gmail.com                |         | 2023-04-02 19:58:44.432+00 |               | +18125555555  | 1994/11/07 |           0 |               0 |                  0 |             2
 230 |       | 9999@gmail.com                |         | 2023-04-02 19:58:44.432+00 |               |               |            |           0 |               1 |                  0 |             0
 229 |       | 7112@gmail.com                |         | 2023-04-02 19:45:25.49+00  |               | +19098003700  | 1994/01/11 |           0 |               0 |                  0 |             1

现在需要每个id仅保留一行汇总数据，规则如下：

1. last_updated取该用户对应的最新值；
2. name、surname、phone_no、phone、birthday取对应最新last_updated的非空值，若无则为NULL；
3. 所有count字段为该用户对应字段的总和。

以id为230的用户为例，期望结果如下：

id  | name  |             email             | surname |        last_updated        |      phone    |  phone_no     | birthday   | order_count | email_col_count | review_track_count | loyalty_count 
-----+-------+-------------------------------+---------+----------------------------+---------------+---------------+------------+-------------+-----------------+--------------------+---------------
 230 |       | 9999@gmail.com                |         | 2023-04-02 19:58:44.432+00 |               | +18125555555  | 1994/11/07  |           4 |              1 |                  0 |             2

现有查询代码如下：

SELECT id, 
       name, 
       email, 
       surname, 
       last_updated, 
       phone, 
       phone_no, 
       birthday, 
       Sum(order_count)        AS order_count, 
       Sum(email_col_count)    AS email_col_count, 
       Sum(review_track_count) AS review_track_count, 
       Sum(loyalty_count)      AS loyalty_count 
FROM     ( 
                SELECT   u.id, 
                         u.name, 
                         u.email, 
                         u.surname, 
                         'order_user'           AS type, 
                         Max(u."updatedAt")     AS last_updated, 
                         Max(ord."phoneNumber") AS phone, 
                         NULL                   AS phone_no, 
                         NULL                   AS birthday, 
                         Count(DISTINCT ord.id) AS order_count, 
                         0                      AS email_col_count, 
                         0                      AS review_track_count, 
                         0                      AS loyalty_count 
                FROM     users u 
                JOIN     orders ord 
                ON       u.id=ord."orderUserId" 
                AND      ord."restaurantTableId" IN (12,7,9,8,10,11,14,99,100,6) 
                GROUP BY u.id, 
                         type 
                UNION 
                SELECT   u.id, 
                         u.name, 
                         u.email, 
                         u.surname, 
                         'email_collection'     AS type, 
                         Max(u."updatedAt")     AS last_updated, 
                         NULL                   AS phone, 
                         NULL                   AS phone_no, 
                         NULL                   AS birthday, 
                         0                      AS order_count, 
                         Count(DISTINCT col.id) AS email_col_count, 
                         0                      AS review_track_count, 
                         0                      AS loyalty_count 
                FROM     users u 
                JOIN     "userEmailCollections" col 
                ON       u.id=col."userId" 
                AND      col."restaurantId" = 6 
                GROUP BY u.id, 
                         type 
                UNION 
                SELECT   u.id, 
                         u.name, 
                         u.email, 
                         u.surname, 
                         'review_track'         AS type, 
                         Max(u."updatedAt")     AS last_updated, 
                         NULL                   AS phone, 
                         NULL                   AS phone_no, 
                         NULL                   AS birthday, 
                         0                      AS order_count, 
                         0                      AS email_col_count, 
                         Count(DISTINCT rev.id) AS review_track_count, 
                         0                      AS loyalty_count 
                FROM     users u 
                JOIN     "reviewTracks" rev 
                ON       u.email=rev."email" 
                AND      rev."restaurantId" = 6 
                GROUP BY u.id, 
                         type 
                UNION 
                SELECT   u.id, 
                         u.name, 
                         u.email, 
                         u.surname, 
                         'loyalty_campaign_redemption' AS type, 
                         Max(u."updatedAt")            AS last_updated, 
                         NULL                          AS phone, 
                         Max(loyy."phoneNo")           AS phone_no, 
                         Max(loyy.birthday)            AS birthday, 
                         0                             AS order_count, 
                         0                             AS email_col_count, 
                         0                             AS review_track_count, 
                         Count(DISTINCT loyy.id)       AS loyalty_count 
                FROM     users u 
                JOIN     "loyaltyCampaignRedemptions" loyy 
                ON       u.id=loyy."userId" 
                AND      loyy."restaurantId" = 6 
                GROUP BY u.id, 
                         type ) AS subquery 
GROUP BY id, 
         name, 
         email, 
         surname, 
         type, 
         last_updated, 
         phone, 
         phone_no, 
         birthday 
ORDER BY last_updated DESC limit 50;

解决方案 #

问题核心在于原查询的外层GROUP BY包含了type、phone等字段，导致同一用户会拆分成多行。需要先按用户ID汇总统计count字段，再获取用户最新的基础信息，最后关联两者得到结果。

修改后的SQL代码： #

WITH user_counts AS (
    -- 第一步：按用户ID汇总所有count字段的总和
    SELECT 
        id,
        email,
        SUM(order_count) AS order_count,
        SUM(email_col_count) AS email_col_count,
        SUM(review_track_count) AS review_track_count,
        SUM(loyalty_count) AS loyalty_count
    FROM (
        SELECT 
            u.id,
            u.email,
            COUNT(DISTINCT ord.id) AS order_count,
            0 AS email_col_count,
            0 AS review_track_count,
            0 AS loyalty_count
        FROM users u
        JOIN orders ord ON u.id = ord."orderUserId"
            AND ord."restaurantTableId" IN (12,7,9,8,10,11,14,99,100,6)
        GROUP BY u.id, u.email

        UNION ALL

        SELECT 
            u.id,
            u.email,
            0 AS order_count,
            COUNT(DISTINCT col.id) AS email_col_count,
            0 AS review_track_count,
            0 AS loyalty_count
        FROM users u
        JOIN "userEmailCollections" col ON u.id = col."userId"
            AND col."restaurantId" = 6
        GROUP BY u.id, u.email

        UNION ALL

        SELECT 
            u.id,
            u.email,
            0 AS order_count,
            0 AS email_col_count,
            COUNT(DISTINCT rev.id) AS review_track_count,
            0 AS loyalty_count
        FROM users u
        JOIN "reviewTracks" rev ON u.email = rev."email"
            AND rev."restaurantId" = 6
        GROUP BY u.id, u.email

        UNION ALL

        SELECT 
            u.id,
            u.email,
            0 AS order_count,
            0 AS email_col_count,
            0 AS review_track_count,
            COUNT(DISTINCT loyy.id) AS loyalty_count
        FROM users u
        JOIN "loyaltyCampaignRedemptions" loyy ON u.id = loyy."userId"
            AND loyy."restaurantId" = 6
        GROUP BY u.id, u.email
    ) AS sub
    GROUP BY id, email
),
user_latest_info AS (
    -- 第二步：获取每个用户最新的基础信息（last_updated最大的记录中的非空值）
    SELECT 
        id,
        name,
        surname,
        last_updated,
        phone,
        phone_no,
        birthday
    FROM (
        SELECT 
            u.id,
            u.name,
            u.surname,
            MAX(u."updatedAt") OVER (PARTITION BY u.id) AS last_updated,
            -- 优先取最新更新记录中的非空phone值
            FIRST_VALUE(ord."phoneNumber") OVER (
                PARTITION BY u.id 
                ORDER BY u."updatedAt" DESC 
                ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
            ) AS phone,
            -- 优先取最新更新记录中的非空phone_no值
            FIRST_VALUE(loyy."phoneNo") OVER (
                PARTITION BY u.id 
                ORDER BY u."updatedAt" DESC 
                ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
            ) AS phone_no,
            -- 优先取最新更新记录中的非空birthday值
            FIRST_VALUE(loyy.birthday) OVER (
                PARTITION BY u.id 
                ORDER BY u."updatedAt" DESC 
                ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
            ) AS birthday,
            ROW_NUMBER() OVER (PARTITION BY u.id ORDER BY u."updatedAt" DESC) AS rn
        FROM users u
        LEFT JOIN orders ord ON u.id = ord."orderUserId"
            AND ord."restaurantTableId" IN (12,7,9,8,10,11,14,99,100,6)
        LEFT JOIN "loyaltyCampaignRedemptions" loyy ON u.id = loyy."userId"
            AND loyy."restaurantId" = 6
        -- 关联其他表保证能获取到所有可能的字段值
        LEFT JOIN "userEmailCollections" col ON u.id = col."userId"
            AND col."restaurantId" = 6
        LEFT JOIN "reviewTracks" rev ON u.email = rev."email"
            AND rev."restaurantId" = 6
    ) AS latest
    WHERE rn = 1
)
-- 第三步：关联汇总的count信息和最新基础信息
SELECT 
    ul.id,
    ul.name,
    uc.email,
    ul.surname,
    ul.last_updated,
    ul.phone,
    ul.phone_no,
    ul.birthday,
    uc.order_count,
    uc.email_col_count,
    uc.review_track_count,
    uc.loyalty_count
FROM user_latest_info ul
JOIN user_counts uc ON ul.id = uc.id
ORDER BY ul.last_updated DESC
LIMIT 50;

关键修改说明： #

1. 拆分逻辑：用CTE将统计和取最新信息拆分为两个独立步骤，逻辑更清晰；
2. 汇总count字段：user_counts CTE专门按用户ID汇总所有count字段的总和，避免原查询中因GROUP BY其他字段导致的拆分；
3. 获取最新基础信息：user_latest_info CTE使用窗口函数MAX()获取用户最新的last_updated，用FIRST_VALUE()按更新时间倒序取非空的基础字段值，最后通过ROW_NUMBER()筛选每个用户的第一条记录；
4. 关联结果：将两个CTE关联，得到每个用户一行的最终汇总数据。

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