假設您有三個表（用戶、評論、喜歡）
SELECT *
FROM user u
JOIN (SELECT user_id, count(*) as likes
FROM likes l
WHERE l.created_on > '2021-10-01' AND l.created_on <= '2021-10-31'
GROUP BY l.user_id
) as likes ON u.id = likes.user_id
JOIN (
SELECT user_id, count(*) as comments
FROM comment c
WHERE c.created_on > '2021-10-01' AND c.created_on <= '2021-10-31'
) as comments ON u.id = comments.user_id;
有沒有辦法簡化這樣的查詢？created_on 過濾器日期可以宣告為變數嗎？
uj5u.com熱心網友回復：
PL/pgSQL 支持函式內部的變數，因此您可以根據需要將其設為函式，然后查詢函式結果。像這樣說：
CREATE FUNCTION get_user_interaction_counts
(
date_start date,
date_end date
)
RETURNS TABLE(user_id int, like_count int, comment_count int) as $$
SELECT u.id, likes.likes, comments.comments
FROM "user" u
JOIN (SELECT user_id, count(*) as likes
FROM likes l
WHERE l.created_on > date_start AND l.created_on <= date_end
GROUP BY l.user_id
) as likes ON u.id = likes.user_id
JOIN (
SELECT user_id, count(*) as comments
FROM comment c
WHERE c.created_on > date_start AND c.created_on <= date_end
GROUP BY c.user_id
) as comments ON u.id = comments.user_id
$$ LANGUAGE SQL;
然后你會查詢它：
select * from get_user_interaction_counts('2021-10-01', '2021-10-30')
當然，這可能不是你所追求的。如果您在“僅查詢”時需要變數，則取決于您使用的客戶端。如果您使用的是 psql，它確實支持 variables。其他客戶可能支持也可能不支持，這取決于。
您還可以做的是稍微修改查詢，就像這樣，在開頭使用引數 CTE，然后使用它來加入選定的引數：
WITH parameters AS (
SELECT '2021-10-01'::date as date_start
, '2021-10-30'::date as date_end
)
SELECT u.id, likes.likes, comments.comments
FROM "user" u
JOIN (SELECT user_id, count(*) as likes
FROM likes l
JOIN parameters par
ON l.created_on > par.date_start AND l.created_on <= par.date_end
GROUP BY l.user_id
) as likes ON u.id = likes.user_id
JOIN (
SELECT user_id, count(*) as comments
FROM comment c
JOIN parameters par
ON c.created_on > par.date_start AND c.created_on <= par.date_end
GROUP BY c.user_id
) as comments ON u.id = comments.user_id
這是一個 dbfiddle 演示
PS。您的查詢使用內部聯接，因此只會包括喜歡和評論的用戶。如果用戶沒有評論或不喜歡任何內容，他們將被排除在外，因為連接查詢不會回傳任何記錄。
轉載請註明出處，本文鏈接：https://www.uj5u.com/qiye/464758.html
標籤：PostgreSQL