在不同的表上连接两个相似的查询 - MySQL OR

Question

我在MySQL查询方面遇到了问题（由于我是新手，请轻善待我！）。我的网站上有电视节目，并希望根据两个不同的操作（用于自定义时间表），选择两个日期之间播出的剧集（没有问题）。基本上，如果用户正在关注（uses_follow_shows）或观看该节目（user_watched），我想显示剧集，因此我认为会像下面这样有（query_on_follow OR query_on_watch)，但是这并不起作用（我得到了1200行而不是3行，它从user_watched中获取了每一行现有数据）。

我以为这个语句可以解决问题，但它并不能解决，我不明白原因。

SELECT * FROM show_episode_airdate, show_episode, show_network, network, shows, users_follow_shows, user_watched
                                              WHERE show_episode_airdate.airdate BETWEEN '2013-07-20' AND '2013-07-27' 
                                              AND ( show_episode.episode_id = show_episode_airdate.episode_id 
                                              AND shows.id = show_network.show_id
                                              AND show_network.network_id = network.network_id 
                                              AND show_episode.imdb_id_show = shows.imdb_id 
                                              AND users_follow_shows.user_id = 2 AND shows.id = users_follow_shows.show_id  

                                              OR user_watched.user_id = 2 
                                              AND shows.id = user_watched.show_id 
                                              AND show_episode.episode_id = show_episode_airdate.episode_id 
                                              AND shows.id = show_network.show_id
                                              AND show_network.network_id = network.network_id 
                                              AND show_episode.imdb_id_show = shows.imdb_id )
                                              ORDER by network.network_id ASC

有人能告诉我问题出在哪吗？

谢谢！

编辑：

SELECT *
 FROM show_episode_airdate join
 show_episode 
 on show_episode.episode_id = show_episode_airdate.episode_id join
 shows
 on shows.imdb_id = show_episode.imdb_id_show join
 show_network
 on show_network.show_id = shows.id join
 network
 on show_network.network_id = network.network_id join
 users_follow_shows
 on shows.id = users_follow_shows.show_id join
 user_watched
 on shows.id = user_watched.show_id 
WHERE show_episode_airdate.airdate BETWEEN '2013-07-20' AND '2013-07-27' and
  (users_follow_shows.user_id = 2  or
   user_watched.user_id = 2
  )
 ORDER by network.network_id ASC;

- Callombert

Answer 1

如果您使用正确的连接语法编写此代码，则逻辑问题将消失。结果类似于这样：

SELECT *
FROM show_episode_airdate join
     show_episode 
     on show_episode.episode_id = show_episode_airdate.episode_id join
     show_network 
     on show_network.show_id = network.show_id join     <------- THIS IS NOT IN YOUR ORIGINAL LIST
     network
     on show_network.network_id = network.network_id join
     user_follows_shows
     on shows.id = users_follow_shows.show_id join
     user_watched
     on shows.id = user_watched.show_id 
WHERE show_episode_airdate.airdate BETWEEN '2013-07-20' AND '2013-07-27' and
      (users_follow_shows.user_id = 2  or
       user_watched.user_id = 2
      )
ORDER by network.network_id ASC;

什么是好处？这个查询更易于理解，更易于编写，更易于理解，并且不太容易出现可怕的错误，比如遗漏连接条件。SQL引擎更容易优化它，应该运行得更快。使用适当的联接语法。

仿佛强调了我的观点，原始查询有7个表，但只有5个联接条件。这通常意味着您正在表之间执行笛卡尔积。在这种情况下，我认为它缺少show_network和shows之间的关系。

编辑：

现在至少，这个查询相当易于理解。问题是用户可能会关注一些节目并观看一些节目。您的查询在这两个集合之间产生笛卡尔积。更糟糕的是，如果一个节目没有关注者或没有观众，那么它将被简单地删除。

解决问题的一种方法是将查询分成两个部分。相反，我将在select子句中使用left outer join和distinct：

SELECT distinct *
 FROM show_episode_airdate join
 show_episode 
 on show_episode.episode_id = show_episode_airdate.episode_id join
 shows
 on shows.imdb_id = show_episode.imdb_id_show join
 show_network
 on show_network.show_id = shows.id join
 network
 on show_network.network_id = network.network_id left outer join
 users_follow_shows
 on shows.id = users_follow_shows.show_id left outer join
 user_watched
 on shows.id = user_watched.show_id 
WHERE show_episode_airdate.airdate BETWEEN '2013-07-20' AND '2013-07-27' and
  (users_follow_shows.user_id = 2  or
   user_watched.user_id = 2
  )
 ORDER by network.network_id ASC;

"

*应替换为您实际需要的列 -- 仅在避免使用users_follow_shows和user_watched列时，distinct才能起作用。 left outer join意味着将考虑所有节目。

另一种写法是编写子查询以组合观看和关注的节目。这将消除重复项，看起来像：

"

SELECT *
FROM show_episode_airdate join
     show_episode 
     on show_episode.episode_id = show_episode_airdate.episode_id join
     shows
     on shows.imdb_id = show_episode.imdb_id_show join
     show_network
     on show_network.show_id = shows.id join
     network
     on show_network.network_id = network.network_id join
     (select show_id, user_id
      from users_follow_shows
      union
      select show_id, user_id
      from user_watched
     ) watch_or_follow
     on shows.id = watch_or_follow.show_id
WHERE show_episode_airdate.airdate BETWEEN '2013-07-20' AND '2013-07-27' and
      watch_or_follow.user_id = 2
ORDER by network.network_id ASC;

- Gordon Linoff

---

我遇到了这个错误：在“on”子句中未知的列“shows.id”（该列确实存在）。 - Callombert

我以前从未使用过join和on，而且where和and的逻辑更容易理解。但这不是第一次有人提到我应该使用它，所以我一定会尝试弄清楚它的含义。 - Callombert

@metareviewr 你怎么学习SQL却不学习正确的语法呢？你需要找到更好的书籍/教练/在线材料，它们可以更好地教授你需要知道的内容。你的查询缺少一个非常重要的“join”条件。在你写连结时，几乎不可能发现该问题。如果使用正确的语法，这是微不足道的。 - Gordon Linoff

我是通过实践学习的。我学到的大部分东西都是在Stackoverflow上学到的。 - Callombert

无论如何，我已经理解了join和on的原则，并重新制作了上面的查询（请参见原始帖子），但它仍然无法正常工作。它只获取已观看节目的剧集，而不是正在关注的剧集。而且我也有重复的行。 - Callombert

Answer 2

你需要在OR条件中加上()，例如如果这是你的目标：

SELECT * FROM show_episode_airdate, show_episode, show_network, network, shows, users_follow_shows, user_watched
WHERE show_episode_airdate.airdate BETWEEN '2013-07-20' AND '2013-07-27' 
AND ( (show_episode.episode_id = show_episode_airdate.episode_id 
AND shows.id = show_network.show_id
AND show_network.network_id = network.network_id 
AND show_episode.imdb_id_show = shows.imdb_id 
AND users_follow_shows.user_id = 2 
AND shows.id = users_follow_shows.show_id)
OR (user_watched.user_id = 2 
AND shows.id = user_watched.show_id 
AND show_episode.episode_id = show_episode_airdate.episode_id 
AND shows.id = show_network.show_id
AND show_network.network_id = network.network_id 
AND show_episode.imdb_id_show = shows.imdb_id ) )
ORDER by network.network_id ASC

所以你会得到：IF BETWEEN 日期 AND 第一个（条件）OR 第二个（条件）

我建议你使用JOIN/INNER JOIN，在所有这些AND中我迷失了 :D

- mirkobrankovic

---

那仍然返回1200行。我认为这是因为在第一个OR中，它选择了所有的user_watched，而在第二个OR中，选择了所有的users_follow_shows...对吗？ - Callombert

我认为@Gordon Linoff解释得最好:D +不必写整个东西，所以请详细阅读:D - mirkobrankovic

Answer 3

这是由于你在where子句中使用了OR条件。请为组合条件提供适当的括号。

只需尝试以下查询。

SELECT * FROM show_episode_airdate, show_episode, show_network, network, shows, users_follow_shows, user_watched
WHERE show_episode_airdate.airdate BETWEEN '2013-07-20' AND '2013-07-27' 
 AND (( show_episode.episode_id = show_episode_airdate.episode_id 
 AND shows.id = show_network.show_id
 AND show_network.network_id = network.network_id 
 AND show_episode.imdb_id_show = shows.imdb_id 
 AND users_follow_shows.user_id = 2 AND shows.id = users_follow_shows.show_id  )
OR (user_watched.user_id = 2 
AND shows.id = user_watched.show_id 
AND show_episode.episode_id = show_episode_airdate.episode_id 
AND shows.id = show_network.show_id
AND show_network.network_id = network.network_id 
AND show_episode.imdb_id_show = shows.imdb_id ))

ORDER by network.network_id ASC

- Shaikh Farooque