SQL查询以正确的顺序显示2列

Question

在这个Fiddle：sql小提琴中检查我的SQL查询

我想从SQL查询中得到什么？

如您所见，我在recommendations表中插入了3项内容。

1. recommendations_tmdb_id
2. recommendations_title
3. recommendations_vote_average

我使用recommendations_tmdb_id将recommendations表连接到`tmdb_movies表

我想显示recommendations_title和recommendations_vote_average。

在我的原始代码中:这是我显示数据的方式：

The Dark Knight Rises - 7.5
Batman Begins - 7.5
Iron Man - 7.3
The Lord of the Rings: The Return of the King - 8.1

等等。

但是为了简单起见，我不想包含完整的代码，只包含所需的代码。

因此，在SQL篡改中，数据就是这样回显的。

​

​

这段代码的问题：

第一个问题：

仔细看代码。第二个recommendations_vote_average号码是7.5，第二个recommendations_title电影名是Batman Begins

所以，这意味着我的代码显示蝙蝠侠开始电影的vote_average评级为7.5，这是假的。我插入蝙蝠侠的评级开始于7.3而不是7.5。

它正在放映其他电影的评级。所以这意味着，按错误的顺序。

问题2：

它只显示了6个vote_average结果，而不是10个。我插入了10个记录。为什么它只显示6张唱片，而不是10张？因为DISTINCT在我的group_concat行。

如果我删除了DISTINCT怎么办？然后，它将显示对所有电影的7.5评级。

那么，我如何解决这个问题呢？

预期结果：

       recommendations_vote_average                           recommendations_title


7.5, 7.5, 7.3, 8.1, 8, 7.9, 7.9, 8, 6.6, 6.6          The Dark Knight Rises,Batman Begins,Iron Man,The Lord of the Rings: The Return of the King,The Lord of the Rings: The The Fellowship of the Ring,The Lord of the Rings: The Two Towers,The Matrix,Inception,Iron Man 2,Captain America: The First Avenger

SQL完整代码

CREATE TABLE tmdb_movies (
  tmdb_id INTEGER NOT NULL PRIMARY KEY,
  movie_title TEXT NOT NULL
);

INSERT INTO tmdb_movies (tmdb_id, movie_title) VALUES
(1, 'The Dark Knight');


CREATE TABLE recommendations (
  recommendations_tmdb_id INTEGER NOT NULL,
  recommendations_title TEXT NOT NULL,
  recommendations_vote_average TEXT NOT NULL
);




INSERT INTO recommendations (recommendations_tmdb_id, recommendations_title, recommendations_vote_average) VALUES
(1, 'The Dark Knight Rises', '7.5'),
(1, 'Batman Begins', '7.5'),
(1, 'Iron Man', '7.3'),
(1, 'The Lord of the Rings: The Return of the King', '8.1'),
(1, 'The Lord of the Rings: The The Fellowship of the Ring', '8'),
(1, 'The Lord of the Rings: The Two Towers', '7.9'),
(1, 'The Matrix', '7.9'),
(1, 'Inception', '8'),
(1, 'Iron Man 2', '6.6'),
(1, 'Captain America: The First Avenger', '6.6');


SELECT tmdb_movies.movie_title
,GROUP_CONCAT(DISTINCT recommendations.recommendations_vote_average) as recommendations_vote_average
,GROUP_CONCAT(DISTINCT recommendations.recommendations_title) as recommendations_title
FROM tmdb_movies 

LEFT JOIN recommendations ON recommendations.recommendations_tmdb_id=tmdb_movies.tmdb_id

Where tmdb_movies.tmdb_id=1

GROUP BY tmdb_movies.movie_title

Answer 1

从GROUP_CONCAT()中删除DSTINCT

CREATE TABLE tmdb_movies (
  tmdb_id INTEGER NOT NULL PRIMARY KEY,
  movie_title TEXT NOT NULL
);

INSERT INTO tmdb_movies (tmdb_id, movie_title) VALUES
(1, 'The Dark Knight');


CREATE TABLE recommendations (
  recommendations_tmdb_id INTEGER NOT NULL,
  recommendations_title TEXT NOT NULL,
  recommendations_vote_average TEXT NOT NULL
);




INSERT INTO recommendations (recommendations_tmdb_id, recommendations_title, recommendations_vote_average) VALUES
(1, 'The Dark Knight Rises', '7.5'),
(1, 'Batman Begins', '7.5'),
(1, 'Iron Man', '7.3'),
(1, 'The Lord of the Rings: The Return of the King', '8.1'),
(1, 'The Lord of the Rings: The The Fellowship of the Ring', '8'),
(1, 'The Lord of the Rings: The Two Towers', '7.9'),
(1, 'The Matrix', '7.9'),
(1, 'Inception', '8'),
(1, 'Iron Man 2', '6.6'),
(1, 'Captain America: The First Avenger', '6.6');


SELECT tmdb_movies.movie_title
,GROUP_CONCAT(recommendations.recommendations_vote_average) as recommendations_vote_average
,GROUP_CONCAT(recommendations.recommendations_title) as recommendations_title
FROM tmdb_movies ;

返回

    movie_title recommendations_vote_average    recommendations_title
1   The Dark Knight 7.5,7.5,7.3,8.1,8,7.9,7.9,8,6.6,6.6 The Dark Knight Rises,Batman Begins,Iron Man,The Lord of the Rings: The Return of the King,The Lord of the Rings: The The Fellowship of the Ring,The Lord of the Rings: The Two Towers,The Matrix,Inception,Iron Man 2,Captain America: The First Avenger

http://rextester.com/NNJA85394