如何停止某些记录在SQL查询中多次显示 [英] How to stop some records displaying multiple times in SQL Query

问题描述

这是完整的SQL小提琴

如您所见，recommendations表中的记录显示多次.但是，如果我删除此行LEFT JOIN cast ON cast.cast_tmdb_id=tmdb_movies.tmdb_id 然后，它们不会显示多次.因此，这条线是主要问题.

As you can see, the records from recommendations table are displaying multiple times. But If i remove this line LEFT JOIN cast ON cast.cast_tmdb_id=tmdb_movies.tmdb_id then, they do not display multiple times. So this line is the main problem.

为了简单起见，我没有在SQL查询中使用强制转换表.但是我需要.

I did not use cast table in my SQL Query, just for the sake of simplicity. But I need it.

是的，我需要group_concat才能在我的PHP code

And yes, I need group_concat to display the records together in my PHP code

我想这就是所需的全部信息.如果您需要更多信息或希望我再次解释，请告诉我.

That's all the information needed, i guess. Please let me know, if you need more information or want me to explain again.

亲爱的@Shadow，他将此建议标记为重复.如果使用DISTINCT，则不会显示所有记录.因为可能有2部电影的平均评分为7.5

Dear @Shadow, who marked this suggestion as duplicate. If I use DISTINCT then, not all records will display. Because it is possible that 2 movies have an average rating of 7.5

推荐答案

您的问题并不十分清楚.那是因为您没有显示期望的结果.

Your question is not really clear. That is because you have not shown the desired result.

在查询中，您将选择一列推荐的电影标题，以及另一列具有投票权的电影.但是，这些没有关系，因为您未指定订单.因此，例如，推荐的第一部电影可能会显示第五票.即使选票是按电影排序的，也仍然很难阅读.

In your query you are selecting a column of recommended movie titles and another with votes. However, these are not related, as you didn't specify an order. So the first recommended movie may have the fifth vote shown for instance. And even if the votes were sorted by movie, that would still be hard to read.

因此，从理想的结果开始是这样.然后再写查询.

So start with how the result would ideally look like. Only then write the query.

示例:

预期结果:

movie_title       | roles                          | recommendations
------------------+--------------------------------+------------------------
The Dark Knight   | Christian Bale (Bruce Wayne /  | The Dark Knight Rises (7.5),
                  | Batman), Michael Caine (Alfred | Batman Begins (7.5), Iron 
                  | Pennyworth), ...               | Man (7.3), ...

因此，我们需要演员表(每个电影的所有角色)和推荐表(每个电影的所有推荐)的聚集.首先进行汇总，然后加入结果:

So we need an aggreation from the cast table (all roles per movie) and one from the recommendation table (all recommendations per movie). Do the aggregations first and then join the results:

SELECT 
  m.movie_title,
  c.roles,
  r.movies as recommendations
FROM tmdb_movies m
LEFT JOIN
(
  SELECT 
    cast_tmdb_id as movie_id,
    GROUP_CONCAT(CONCAT(cast_name, ' (', cast_character, ')') SEPARATOR ', ') as roles
  FROM cast
  GROUP BY cast_tmdb_id
) c ON c.movie_id = m.tmdb_id
LEFT JOIN
(
  SELECT 
    recommendations_tmdb_id as movie_id,
    GROUP_CONCAT(CONCAT(recommendations_title, ' (', recommendations_vote_average, ')')
                 SEPARATOR ', ') as movies
  FROM recommendations
  GROUP BY recommendations_tmdb_id
) r ON r.movie_id = m.tmdb_id
WHERE m.tmdb_id = 1;

这是rextester链接: http://rextester.com/FHA48503

Here is the rextester link: http://rextester.com/FHA48503

这篇关于如何停止某些记录在SQL查询中多次显示的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！