此查詢有效：
SELECT
COUNT(video_views.user_id) AS view_count,
video_feed_unscored.*
FROM video_feed_unscored
LEFT JOIN video_user_interaction video_views ON (video_views.video_id = video_feed_unscored.id)
WHERE video_views.user_id = '8601ab73-d742-429e-b8e3-ba349725e5f5'
GROUP BY
video_feed_unscored.id,
video_feed_unscored.title,
video_feed_unscored.username,
video_feed_unscored.user_id,
video_feed_unscored.video_rating_id,
video_feed_unscored.mux_asset_id,
video_feed_unscored.mux_playback_id,
video_feed_unscored.days_old,
video_feed_unscored.view_start_count,
video_feed_unscored.view_5seconds_count,
video_feed_unscored.like_count
;
但它非常明確，我希望它簡化為：
SELECT
COUNT(video_views.user_id) AS view_count,
video_feed_unscored.*
FROM video_feed_unscored
LEFT JOIN video_user_interaction video_views ON (video_views.video_id = video_feed_unscored.id)
WHERE video_views.user_id = '8601ab73-d742-429e-b8e3-ba349725e5f5'
GROUP BY
video_feed_unscored.id
;
但這給出了錯誤column "video_feed_unscored.title" must appear in the GROUP BY clause or be used in an aggregate function。
還有其他方法可以簡化查詢嗎？
uj5u.com熱心網友回復：
也許不是simplify，但假設有很多提要，而用戶只看到其中的幾個，下面的查詢應該有更好的執行計劃（值得檢查）。
另外選擇列任何范圍的列不再是問題
SELECT
view_count,
video_feed_unscored.*
FROM video_feed_unscored
LEFT JOIN (
select
count(*) view_count,
b.video_id
from video_user_interaction b
where
b.user_id = '8601ab73-d742-429e-b8e3-ba349725e5f5'
group by b.video_id
) c on (c.video_id = video_feed_unscored.id)
并且總是有子查詢的地方（如果執行計劃足夠好）
SELECT
(SELECT
COUNT(video_views.user_id)
FROM video_user_interaction video_views
WHERE
video_views.user_id = '8601ab73-d742-429e-b8e3-ba349725e5f5'
AND video_views.video_id = video_feed_unscored.id
) AS view_count,
video_feed_unscored.*
FROM video_feed_unscored
轉載請註明出處，本文鏈接：https://www.uj5u.com/ruanti/492782.html
標籤：sql PostgreSQL
上一篇：計算兩行之間的百分比差異