我有以下查询,它整理了几个表中的数据,并以以下格式生成结果:
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
只有一行,数据整理如下:
last_updated
应该是该用户last_updated
列中的最新值name
、surname
、phone_no
、phone
、birthday
应该是最近的(最新的last_updated
)NOT NULL值(如果存在),否则为NULL
1.所有count
字段都应该是给定用户的相应计数字段的总和。
因此,对于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;
1条答案
按热度按时间ni65a41a1#
例如,你可以使用
FIRST_VALUE
,试试下面的代码: