我有表格 - 游戏、game_views、game_views_game_links、流派、games_genres_links。
我需要计算 game_views 的观看次数,按它们进行过滤并将流派表连接到流派字段。
如果我只使用 2 个与 game_views、game_views_game_links 的连接,我会给出正常的结果。
我的查询是给所有类型 1 个视图,我可以将其分开吗?
关系:
- 多对多类型的游戏
- 一对多游戏 game_views
此查询给出正确的结果
SELECT "games"."id" AS "id",
COUNT("game_views"."id") AS "views",
"games"."created_at",
"games"."updated_at"
FROM "games"
LEFT JOIN "game_views_game_links" AS "gvgl" ON "games"."id" = "gvgl"."game_id"
LEFT JOIN "game_views" ON "game_views"."id" = "gvgl"."game_view_id"
WHERE "game_views"."created_at" > '2023-03-13T12:00:02+03:00'
GROUP BY "games"."id"
ORDER BY "views" DESC,
"updated_at" ASC
这个查询加倍
SELECT "games"."id" AS "id",
COUNT("game_views"."id") AS "views",
"games"."created_at",
"games"."updated_at"
FROM "games"
LEFT JOIN "game_views_game_links" AS "gvgl" ON "games"."id" = "gvgl"."game_id"
LEFT JOIN "game_views" ON "game_views"."id" = "gvgl"."game_view_id"
LEFT JOIN "games_genres_links" AS "ggl" ON "games"."id" = "ggl"."game_id"
LEFT JOIN "genres" ON "genres"."id" = "ggl"."genre_id"
WHERE "game_views"."created_at" > '2023-03-13T12:00:02+03:00'
GROUP BY "games"."id"
ORDER BY "views" DESC,
"updated_at" ASC
将视图拆分为子查询
您需要在加入之前进行聚合。请注意视图的使用
JOIN
而不是LEFT JOIN
视图,因为您的过滤器无论如何都在有效地执行此操作。