SQL：控制返回的记录数 [英] SQL: Controlling how many records get returned

问题描述

我有一个电影数据库，有电影，演员和导演的桌子。每个电影在电影表中占据单个行。几乎所有的查询都需要连接到其他表，使得带有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.

这篇关于SQL：控制返回的记录数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！