SQL：控制返回的记录数

本文介绍了SQL：控制返回的记录数的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我有一个电影数据库，有电影，演员和导演的桌子。每个电影在电影表中占据单个行。几乎所有的查询都需要连接到其他表，使得带有LIMIT 50，offset 0的查询可以返回大约4部电影的完整数据。下面是一个示例查询。如何修改此设置以确保正好获取10部电影的数据？

选择movie.id，movie.title，star.name，star.name_url，dir.name， dir .name_url，genre.name，genre.name_url FROM movie LEFT JOIN actor ON（movie.id = actor.movi​​e_id） LEFT JOIN person AS star ON（actor.person_id = star.id） LEFT JOIN董事 ON（movie.id = director.movi​​e_id） LEFT JOIN人员AS dir ON（director.person_id = dir.id） LEFT JOIN genre_classification ON（movie.id = genre_classification.movi​​e_id） LEFT JOIN类型 ON（genre_classification.genre_id = genre.id） WHERE（movie.id> 0） ORDER BY movie.id LIMIT 50 OFFSET 0;

我使用的PostgresSQL可能没有关系。

选择movie.id ，movie.title，star.name，star.name_url，dir.name， dir.name_url，genre.name，genre.name_url FROM （SELECT * FROM movie WHERE movie.id > 0 ORDER BY movie.id LIMIT 10）movie LEFT JOIN演员 ON（movie.id = actor.movi​​e_id） LEFT加入人物as star ON（演员。 person_id = star.id） LEFT JOIN director ON（movie.id = director.movi​​e_id） LEFT JOIN person AS dir ON（director.person_id = dir.id） LEFT JOIN genre_classification ON（movie.id = genre_classification.movi​​e_id） LEFT JOIN类型 ON（genre_classification.genre_id = genre.id）

编辑：通过将所有条件放入子选择，您无法控制源表 code>将用于JOIN。性能方面，这也应该快得多。

I have a film database with tables for movies, actors and directors. Each movie occupies a single row in the movie table. Almost all my queries require joins to other tables such that a query with LIMIT 50, offset 0 returns complete data for about 4 movies. Below is a sample query. How can I modify this to ensure getting data for exactly 10 movies back?

SELECT movie.id, movie.title, star.name, star.name_url, dir.name, dir.name_url, genre.name, genre.name_url FROM movie LEFT JOIN actor ON (movie.id = actor.movie_id) LEFT JOIN person AS star ON (actor.person_id = star.id) LEFT JOIN director ON (movie.id = director.movie_id) LEFT JOIN person AS dir ON (director.person_id = dir.id) LEFT JOIN genre_classification ON (movie.id = genre_classification.movie_id) LEFT JOIN genre ON (genre_classification.genre_id = genre.id) WHERE (movie.id > 0) ORDER BY movie.id LIMIT 50 OFFSET 0;

I am using PostgresSQL which probably doesn't matter.

解决方案

There you go (untested):

SELECT movie.id, movie.title, star.name, star.name_url, dir.name, dir.name_url, genre.name, genre.name_url FROM (SELECT * FROM movie WHERE movie.id > 0 ORDER BY movie.id LIMIT 10) movie LEFT JOIN actor ON (movie.id = actor.movie_id) LEFT JOIN person AS star ON (actor.person_id = star.id) LEFT JOIN director ON (movie.id = director.movie_id) LEFT JOIN person AS dir ON (director.person_id = dir.id) LEFT JOIN genre_classification ON (movie.id = genre_classification.movie_id) LEFT JOIN genre ON (genre_classification.genre_id = genre.id)

Edit: By putting all your conditions into the subselect, you cant control what data from the source table movie will be used for the JOIN. Performance wise, this also should be much faster.