MySql LIMIT+JOIN正在阻止返回数据

Question

我有两个表，正在尝试返回分配给特定cinemaId的所有配置文件。

1. 内容_内容
   • 保存有关content_profile（关联

表：

+----------------------+--------------+------+-----+---------+----------------+
| Field                | Type         | Null | Key | Default | Extra          |
+----------------------+--------------+------+-----+---------+----------------+
| id                   | int(11)      | NO   | PRI | NULL    | auto_increment |
| title                | varchar(200) | NO   |     | NULL    |                |
| slug                 | varchar(100) | NO   | MUL | NULL    |                |
| author_id            | int(11)      | YES  | MUL | NULL    |                |
| descr                | longtext     | NO   |     | NULL    |                |            |
| layout_type          | varchar(32)  | NO   |     |         |                |             |
| is_feature           | tinyint(1)   | NO   |     | NULL    |                |
| main_feature         | tinyint(1)   | NO   |     | NULL    |                |
| postcode_id          | int(11)      | YES  | MUL | NULL    |                |
| cinemaId             | int(11)      | YES  |     | NULL    |                |
+----------------------+--------------+------+-----+---------+----------------+

2. 内容_文件
   • 保存关于个人资料的特定数据（将个人资料视为开设我们的商店等）

表：

+------------------------+----------------------+------+-----+---------+-------+
| Field                  | Type                 | Null | Key | Default | Extra |
+------------------------+----------------------+------+-----+---------+-------+
| content_ptr_id         | int(11)              | NO   | PRI | NULL    |       |
| body                   | longtext             | NO   |     | NULL    |       |
| web_site               | varchar(200)         | NO   |     | NULL    |       |
| email                  | varchar(75)          | NO   |     | NULL    |       |   |
| hours                  | longtext             | NO   |     | NULL    |       |
| price_range            | smallint(5) unsigned | YES  | MUL | NULL    |       | |
| primary_category_id    | int(11)              | NO   |     | NULL    |       |   |
+------------------------+----------------------+------+-----+---------+-------+

我想做的是select * content_profile where content_content.cinemaId = 2。

我构建了以下查询，它将两个表以及一个content_image表连接在一起，该表为单个content_profile.content_ptr_id保存0个图像。注意，我把images表排除在外，因为它与这个问题没有任何关系。

> select 'profile'.'content_ptr_id' AS
> 'profile.content_ptr_id','profile'.'body' AS
> 'profile.body','profile'.'web_site' AS
> 'profile.web_site','profile'.'email' AS
> 'profile.email','profile'.'hours' AS
> 'profile.hours','profile'.'price_range' AS
> 'profile.price_range','profile'.'price_range_high' AS
> 'profile.show_in_directory','image'.'id' AS
> 'image.id','image'.'content_id' AS 'image.content_id','image'.'type'
> AS 'image.type','image'.'order' AS 'image.order','image'.'caption' AS
> 'image.caption','image'.'author_id' AS
> 'image.author_id','image'.'image' AS 'image.image','image'.'link_url'
> AS 'image.link_url','content'.'id' AS 'content.id','content'.'title'
> AS 'content.title','content'.'slug' AS 'content.slug','content'.'date'
> AS 'content.date','content'.'section' AS
> 'content.section','content'.'author_id' AS
> 'content.author_id','content'.'descr' AS
> 'content.descr','content'.'inline_gallery' AS
> 'content.inline_gallery','content'.'layout_type' AS
> 'content.layout_type','content'.'blog_id' AS
> 'content.main_feature','content'.'miffCinemaId' AS
> 'content.cinemaId'  from (select
> 'content_profile'.'content_ptr_id','content_profile'.'body','content_profile'.'web_site','content_profile'.'email','content_profile'.'hours','content_profile'.'price_range','content_profile'.'price_range_high','content_profile'.'primary_category_id',
> FROM content_profile LIMIT 10 OFFSET 0) AS profile  LEFT JOIN
> 'content_content' AS 'content' ON 'profile'.'content_ptr_id' =
> 'content'.'id'  LEFT JOIN 'content_image' AS 'image' ON
> 'profile'.'content_ptr_id' = 'image'.'content_id'  WHERE
> content.cinemaId  =  '2' order by  profile.content_ptr_id ASC,
> content.date DESC

我已经把我的问题缩小到这一行：

FROM content_profile LIMIT 10 OFFSET 0

出于某种原因，LIMIT 10 OFFSET 0正在阻止返回任何内容。。。如果删除这两个字段，则返回所有数据。请注意，它们只有3-4个字段带有cinemaId = 2。。。所以限制0，10应该100%显示正确的东西吗？

编辑：

如果我把limit 10 offset 0放在查询的末尾，我将只接收来自1个配置文件的数据，这取决于有多少content_image与它相关

我在subquery中有limit 10 offset 0的原因是，我得到了第一个10 content_profile的的结果

Answer 1

您必须将LIMIT 10 OFFSET 0移动到此查询的末尾。因为在您的查询中，它将在您通过WHERE条件筛选数据之前限制返回数据。

select 'profile'.'content_ptr_id' AS
'profile.content_ptr_id','profile'.'body' AS
'profile.body','profile'.'web_site' AS
'profile.web_site','profile'.'email' AS
'profile.email','profile'.'hours' AS
'profile.hours','profile'.'price_range' AS
'profile.price_range','profile'.'price_range_high' AS
'profile.show_in_directory','image'.'id' AS
'image.id','image'.'content_id' AS 'image.content_id','image'.'type'
AS 'image.type','image'.'order' AS 'image.order','image'.'caption' AS
'image.caption','image'.'author_id' AS
'image.author_id','image'.'image' AS 'image.image','image'.'link_url'
AS 'image.link_url','content'.'id' AS 'content.id','content'.'title'
AS 'content.title','content'.'slug' AS 'content.slug','content'.'date'
AS 'content.date','content'.'section' AS
'content.section','content'.'author_id' AS
'content.author_id','content'.'descr' AS
'content.descr','content'.'inline_gallery' AS
'content.inline_gallery','content'.'layout_type' AS
'content.layout_type','content'.'blog_id' AS
'content.main_feature','content'.'miffCinemaId' AS
'content.cinemaId'  from (select
'content_profile'.'content_ptr_id','content_profile'.'body','content_profile'.'web_site','content_profile'.'email','content_profile'.'hours','content_profile'.'price_range','content_profile'.'price_range_high','content_profile'.'primary_category_id',
FROM content_profile) AS profile  LEFT JOIN
'content_content' AS 'content' ON 'profile'.'content_ptr_id' =
'content'.'id'  LEFT JOIN 'content_image' AS 'image' ON
'profile'.'content_ptr_id' = 'image'.'content_id'  WHERE
content.cinemaId  =  '2' order by  profile.content_ptr_id ASC,
content.date DESC
LIMIT 10 OFFSET 0

Answer 2

您的问题在子查询中。

SELECT
  content_profile.content_ptr_id,
  content_profile.body,
  content_profile.web_site,
  content_profile.email,
  content_profile.hours,
  content_profile.price_range,
  content_profile.price_range_high,
  content_profile.primary_category_id
FROM content_profile
LIMIT 10 OFFSET 0

看，没有任何WHERE子句。该查询将从具有随机content_ptr_id的content_profile返回10行。在这10行中似乎没有cinemaId=2的内容，您的查询总共没有返回任何内容。@Duc Phan是对的：如果你需要限制，你必须把它放在查询之后，而不是子查询中。

但LIMIT永远不会从查询中删除重复项。如果不希望看到重复项，则必须使用GROUP BY或DISTINCT。例如，查询可以如下所示：

SELECT
  profile.content_ptr_id AS 'profile.content_ptr_id',
  profile.body AS 'profile.body',
  profile.web_site AS 'profile.web_site',
  profile.email AS 'profile.email',
  profile.hours AS 'profile.hours',
  profile.price_range AS 'profile.price_range',
  profile.price_range_high AS 'profile.show_in_directory',
  image.id AS 'image.id',
  image.content_id AS 'image.content_id',
  image.type AS 'image.type',
  image.order AS 'image.order',
  image.caption AS 'image.caption',
  image.author_id AS 'image.author_id',
  image.image AS 'image.image',
  image.link_url AS 'image.link_url',
  content.id AS 'content.id',
  content.title AS 'content.title',
  content.slug AS 'content.slug',
  content.date AS 'content.date',
  content.section AS 'content.section',
  content.author_id AS 'content.author_id',
  content.descr AS 'content.descr',
  content.inline_gallery AS 'content.inline_gallery',
  content.layout_type AS 'content.layout_type',
  content.blog_id AS 'content.main_feature',
  content.miffCinemaId AS 'content.cinemaId'
FROM content_profile AS profile -- no subquery needed, limit moved from here
  JOIN content_content AS content ON profile.content_ptr_id = content.id
  LEFT JOIN content_image AS image ON profile.content_ptr_id = image.content_id
WHERE content.cinemaId = 2
GROUP BY profile.content_ptr_id, content.id -- Remove duplicates with GROUP BY
ORDER BY profile.content_ptr_id ASC,
  content.date DESC
LIMIT 10 OFFSET 0

还要注意的是，LEFT JOIN和content_content被替换为JOIN。因为在联接表上具有WHERE content.cinemaId=2过滤器的LEFT JOIN将充当简单的JOIN：如果content_content中没有匹配的行，则content_profile中的行将从结果集中排除。