我有一個看起來像的評論表：
CREATE TABLE comments (
comment_id INT GENERATED ALWAYS AS IDENTITY,
user_id VARCHAR(255) NOT NULL,
username VARCHAR(15) NOT NULL,
content VARCHAR(255) NOT NULL,
created_at TIMESTAMP WITHOUT TIME ZONE DEFAULT now(),
post_id VARCHAR(255) references posts(post_id),
reply_to INT,
);
對于每條評論，我都在嘗試選擇回復的數量。作為回復的評論將有一個reply_to列參考comment_id回復的回復。
我正在嘗試選擇所有回應帖子的列，但是我還想選擇每個評論的回復數。
這是查詢：
SELECT comment_id, username, content,
(
SELECT COUNT(comments.*) FROM comments
JOIN comments comments1 on comments.comment_id = comments1.reply_to
)
as replies FROM comments WHERE post_id = 'uuid';
查詢回傳：
| comment_id | 用戶名 | 內容 | 回復 |
|---|---|---|---|
| 2 | 用戶名1 | 評論內容 | 1 |
| 3 | 用戶名2 | 評論內容 | 1 |
問題是它回傳的兩條評論的回復計數均為 1，即使我只有 1 條記錄的reply_to列為 3。
uj5u.com熱心網友回復：
為什么您的解決方案不起作用
您在 SELECT 子句中使用的子查詢是將所有評論與其所有回復進行內部連接，然后對結果表中的所有結果行進行計數。請注意，您在示例中使用了內部連接（JOIN 是 INNER JOIN 的簡寫），因此如果評論沒有任何回復，則它不會包含在結果表中。所以計數是所有至少有一個回復的評論。
(
-- you are counting ***ALL** the comments in your table inner joined onto their replies.
SELECT COUNT(comments.*) FROM comments
JOIN comments comments1 on comments.comment_id = comments1.reply_to
)
但是您希望將計數限制在每條評論的范圍內，以便僅對他們的回復進行計數，而不是對整個表格進行計數。
解決方案
假設回復只能深入一級，那么我相信您可以使用橫向連接或使用視窗函式的公共表運算式。
以下是如何使用橫向連接來解決它。如果以后有時間，我將使用視窗函式方法更新我的答案。但是我認為平均而言，橫向連接比視窗函式方法更高效，盡管它可能取決于您的資料。
create temporary table comments (
id serial primary key,
post_id int,
reply_to_id int,
body varchar
);
-- the body for each comment illustrates the comment hierarchy for a post
-- POST.PARENT_COMMENT.REPLY. E.g. 1.1.1 means this comment is a reply to the first comment for the first post.
insert into comments (post_id, reply_to_id, body) values
(1, null, '1.1'),
(1, 1, '1.1.1'),
(1, 1, '1.1.2'),
(1, null, '1.2'),
(2, null, '2.1'),
(2, 5, '2.1.1');
-- Lets look at the comments with their replies
select comments.*, replies.* from comments
left outer join comments as replies on comments.id = replies.reply_to_id
order by comments.post_id, replies.reply_to_id ASC;
/*
| COMMENT | REPLY |
id | post_id | reply_to_id | body | id | post_id | reply_to_id | body
---- --------- ------------- ------- ---- --------- ------------- -------
1 | 1 | | 1.1 | 3 | 1 | 1 | 1.1.2
1 | 1 | | 1.1 | 2 | 1 | 1 | 1.1.1
3 | 1 | 1 | 1.1.2 | | | |
2 | 1 | 1 | 1.1.1 | | | |
4 | 1 | | 1.2 | | | |
5 | 2 | | 2.1 | 6 | 2 | 5 | 2.1.1
6 | 2 | 5 | 2.1.1 | | | |
*/
--- now lets get the reply count
select comments.*, r.reply_count from comments
left join lateral (
select count(replies.id) as reply_count from comments as replies
where comments.id = replies.reply_to_id
) as r on true
order by comments.post_id ASC;
/*
id | post_id | reply_to_id | body | reply_count
---- --------- ------------- ------- -------------
1 | 1 | | 1.1 | 2
2 | 1 | 1 | 1.1.1 | 0
3 | 1 | 1 | 1.1.2 | 0
4 | 1 | | 1.2 | 0
5 | 2 | | 2.1 | 1
6 | 2 | 5 | 2.1.1 | 0
*/
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/436666.html
標籤：PostgreSQL 子查询