我正在嘗試計算內部連接。我應該使用子選擇嗎？
const users = db.queryEntries(
"SELECT username, created_at, email, phone FROM users WHERE contactme = 1 ORDER BY created_at DESC"
);
還有一個名為的表searches，它有一個user_id屬性。我想獲取在第一個查詢中回傳的每個用戶的搜索次數。
會是這樣的：
SELECT count(*) as total
FROM searches
WHERE searches.user_id = user.id
...這樣第一個查詢將為total每個用戶回傳
uj5u.com熱心網友回復：
如果我理解正確，您可以嘗試在子查詢中使用COUNTwith GROUP BY，然后JOIN通過 userid執行
SELECT u.username, u.created_at, u.email, u.phone ,s.total
FROM users u
INNER JOIN (
SELECT count(*) as total,user_id
FROM searches
GROUP BY user_id
) s ON s.user_id = u.id
WHERE u.contactme = 1
ORDER BY u.created_at DESC
uj5u.com熱心網友回復：
這可以通過 GROUP BY 子句、COUNT 和 LEFT JOIN 來完成。無需使用子選擇。
SELECT users.username, users.created_at, users.email, users.phone, COUNT(searches.user_id) AS total
FROM users
LEFT JOIN searches on users.id = searches.user_id
GROUP BY users.id, users.username, users.created_at, users.email, users.phone
uj5u.com熱心網友回復：
SELECT COUNT (*) FROM (SELECT * FROM USERS U INNER JOIN SEARCHES S ON S.USER_ID = U.USER_ID) d
轉載請註明出處，本文鏈接：https://www.uj5u.com/qukuanlian/447114.html