獲取用戶的活動
我有一個查詢，留下加入產品及其評論并按評論產品 ID 對它們進行分組。
SELECT product_name, product_description, product_image, count, average_rating, latest_review_date, product_id, user_id
FROM products
LEFT JOIN (
SELECT reviews_product_id, MAX(created_at) AS latest_review_date, COUNT(*), TRUNC(AVG(rating),1) AS average_rating
FROM reviews
GROUP BY reviews_product_id
) reviews on products.product_id = reviews.reviews_product_id
這是回傳的行
Result: -[ RECORD 1 ]------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
product_name | Nescafe
product_description | nice.
product_image | image
count | 6
average_rating | 3.5
latest_review_date | 2021-12-30 14:07:11.158849 08
product_id | 3
user_id | 166e0efb-ee3f-47ab-b8c9-5c9b74187ec4
-[ RECORD 2 ]------- -----------------------------------------------------------------------------
product_name | Zesto
product_description | So Refreshing!
product_image | image
count | 7
average_rating | 2.4
latest_review_date | 2021-12-30 14:07:27.917427 08
product_id | 2
user_id | 166e0efb-ee3f-47ab-b8c9-5c9b74187ec4
資料是正確的，但我也想過濾，以便回傳的行將輸出進行該評論的用戶
SELECT product_name, product_description, product_image, count, average_rating, latest_review_date, product_id, user_id
FROM products
LEFT JOIN (
SELECT reviews_product_id, MAX(created_at) AS latest_review_date, COUNT(*), TRUNC(AVG(rating),1) AS average_rating
FROM reviews
GROUP BY reviews_product_id
) reviews on products.product_id = reviews.reviews_product_id
WHERE reviews.user_id = '1234';
但它告訴我
列 review.user_id 不存在
uj5u.com熱心網友回復：
正如評論中提到的，子查詢不會回傳user_id。將該列添加reviews.user_id = '1234'到子查詢中的 SELECT 后，您可以添加到on條件中，如on products.product_id = reviews.reviews_product_id and reviews.user_id = '1234'.
（我知道這應該是一條評論，但我沒有足夠的聲譽來添加評論:) 現在正在嘗試建立它。）
uj5u.com熱心網友回復：
我需要閱讀更多關于 postgres 的資訊，我使用了不同的連接方法。
用內連接替換左連接作業！
SELECT product_name, product_description, product_image, count, average_rating, latest_review_date, product_id, user_id
FROM products INNER JOIN
(SELECT reviews_product_id, MAX(created_at) AS latest_review_date, COUNT(*), TRUNC(AVG(rating),1) AS average_rating FROM reviews WHERE reviews.user_id = '339d1daa-2f66-46b7-b7bd-bdd980039185' GROUP BY reviews_product_id)
reviews
on products.product_id = reviews.reviews_product_id;
轉載請註明出處，本文鏈接：https://www.uj5u.com/yidong/403732.html
標籤：