我在使用 MySQL 查询时遇到了问题(我是新手，请放轻松!)。我有一个有电视节目的网站，我想根据两个不同的 Action (对于自定义时间表)来选择在两个日期(没问题)之间播出的剧集。基本上，如果用户正在关注(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;

最佳答案

如果您使用正确的连接语法编写此代码，那么逻辑问题就会消失。结果看起来像这样:

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;

* 应该替换为您真正想要的列 - 而 distinct 只有在您避免来自 users_follow_shows 和user_watched。 left outer join 表示将考虑所有节目。

另一种写法——在 select 子句中不需要 distinct——是写一个子查询来组合 watched 和 followed显示。这将删除重复项，看起来像:

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;

关于mysql - 在不同的表上加入两个相似的查询 - MySQL OR，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/17831387/