我試圖創建一個查詢來計算訂閱者的總訂閱者。它目前看起來像這樣：
await this.queryInstance.query(
'SELECT all_users_subbed_to.* , (SELECT COUNT(??????)) AS subscribers_sub_count
FROM
(SELECT publisher_id, subscriber_id, u2.username
AS username, u2.user_photo AS user_photo
FROM subscribers s
INNER JOIN users u
ON (u.id = s.subscriber_id)
INNER JOIN users u2 ON (u2.id = s.publisher_id)
WHERE subscriber_id = ($1)
LIMIT 20
OFFSET ($2))
AS all_users_subbed_to;'
,
[currentUserId = 80, offset]
);
FROM CLAUSE AKAall_users_subbed_to正常作業并顯示當前用戶擁有的所有訂閱者。資料回傳如下：
"subscribedToCurrentUser": [
{
"publisher_id": 84,
"subscriber_id": 80,
"username": "supercoookie",
"user_photo": "profile-pic-for-supercoookie.jpeg"
},
{
"publisher_id": 88,
"subscriber_id": 80,
"username": "GERPAL1",
"user_photo": "profile-pic-for-GERPAL1.jpeg"
}
]
我遇到的問題是獲取這些訂閱者串列的訂閱者總數。我需要使用訂閱者publisher_id ieall_users_subbed_to.publisher_id并從訂閱者表中獲取他們的訂閱總數（使用COUNT）。我想創建一個名為 have 的新列subscribers_sub_count，其中包含該總數。有任何想法嗎？它應該是這樣的：
"subscribedToCurrentUser": [
{
"publisher_id": 84,
"subscriber_id": 80,
"username": "supercoookie",
"user_photo": "profile-pic-for-supercoookie.jpeg",
"subscribers_sub_count": 3
},
{
"publisher_id": 88,
"subscriber_id": 80,
"username": "GERPAL1",
"user_photo": "profile-pic-for-GERPAL1.jpeg",
"subscribers_sub_count": 70
}
]
訂閱者表如下所示： 
uj5u.com熱心網友回復：
await this.queryInstance.query(
'SELECT all_users_subbed_to.*, COUNT(all_users_subbed_to.id) AS subscribers_sub_count
FROM
(SELECT publisher_id, subscriber_id, u2.username
AS username, u2.user_photo AS user_photo
FROM subscribers s
INNER JOIN users u
ON (u.id = s.subscriber_id)
INNER JOIN users u2 ON (u2.id = s.publisher_id)
WHERE subscriber_id = ($1)
LIMIT 20
OFFSET ($2))
AS all_users_subbed_to;'
,
[currentUserId = 80, offset]
);
uj5u.com熱心網友回復：
解決它。它只需要一個使用資料的 WHERE 子句all_users_subbed_to
await this.queryInstance.query(
'SELECT all_users_subbed_to.* ,
(SELECT COUNT(*) FROM subscribers s2 WHERE s2.publisher_id = all_users_subbed_to.publisher_id) AS subscribers_sub_count AS subscribers_sub_count
FROM
(SELECT publisher_id, subscriber_id, u2.username
AS username, u2.user_photo AS user_photo
FROM subscribers s
INNER JOIN users u
ON (u.id = s.subscriber_id)
INNER JOIN users u2 ON (u2.id = s.publisher_id)
WHERE subscriber_id = ($1)
LIMIT 20
OFFSET ($2))
AS all_users_subbed_to;'
,
[currentUserId = 80, offset]
);
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/400107.html