如何回显所有行(与不同的行相关)

Question

这是我的SQL Fiddle

正如您在这里看到的，如果我使用DISTINCT，那么有两个问题

(1)只有第1 recommendations_vote_average色相是正确的。其他所有的数字顺序都不对

2.)只有两个号码被打印出来。

如果我不使用DISTINCT，所有的数字都是7.5 (即第一个vote_average)。

如何以正确的顺序显示所有(10)数字？

预期产出

movie_title       recommendations_vote_average                    recommendations_title
                                                                  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
The Dark Knight   7.5,7.5,7.3,8.1,8,7.9,7.9,8,6.6,6.6

SQL Fiddle代码：

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(a.b)  -- gets all the items in column b -- cardinality preserved

GROUP_CONCAT(DISTINCT a.b) -- distinct values in column b -- cardinality reduced

GROUP_CONCAT(a.b ORDER BY a.b) -- all items in b in order

GROUP_CONCAT(DISTINCT a.b ORDER BY a.b) -- distinct items in b in order

GROUP_CONCAT(a.b ORDER BY a.c) -- all items in b in the same order as c

我不完全确定在最后一个应用程序中添加不同的内容意味着什么。

如果您试图按照相应的顺序获取两个级联列，则不能在其中使用DISTINCT；DISTINCT有可能删除重复的值。

结果集列提到了_average。使用AVG(value)可以得到实际的平均值(算术平均值)。这就给出了一个单一的集合数。

如果您希望在一列中列出分数，而在另一列中想要相应的标题列表，请尝试以下操作。

GROUP_CONCAT(
            recommendations.recommendations_vote_average
   ORDER BY recommendations.recommendations_title
 ) AS recommendations_vote_average,
GROUP_CONCAT(
            recommendations.recommendations_title
   ORDER BY recommendations.recommendations_title
 ) AS recommendations_title

它按照标题的顺序显示两个连接的列表。

您可能没有意识到这一点: DBMS表中的行没有固有的顺序。如果你不止一次地说SELECT * FROM table (没有ORDER BY子句)，而且每次按相同的顺序排列，那么就是个意外。建议表中没有任何东西--例如，唯一的id值--除了分数和标题之外，没有给出这些项目的顺序。所以你可能无法得到你想要的确切的订单。

许多表包含一个自动递增的id列(但您的表没有)。在id子句中使用这样的ORDER BY列是获得可重复排序的一种方法。

专业提示：非规范化数据(例如，列中的逗号分隔数据)通常被认为是有害的。GROUP_CONCAT()将标准化数据(如您的输入)转换为非规范化数据。所以只在你需要的时候才少用它。

Answer 2

为了获得所要求的结果，我认为您所需要做的就是删除不同的内容，但我也建议通过

SELECT
      tmdb_movies.movie_title
    , GROUP_CONCAT(r.recommendations_vote_average
                   ORDER BY r.recommendations_vote_average DESC 
                   SEPARATOR ', ' 
                  ) as recommendations
    , GROUP_CONCAT(r.recommendations_title
                   ORDER BY r.recommendations_vote_average DESC 
                   SEPARATOR ', ' 
                  ) as recommendations
FROM tmdb_movies 
LEFT JOIN recommendations r ON r.recommendations_tmdb_id=tmdb_movies.tmdb_id
Where tmdb_movies.tmdb_id=1
GROUP BY tmdb_movies.movie_title
;

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

如果由我来决定的话，我会把推荐分数和标题结合起来(为演示而添加的手动行间隔)：

+----+-----------------+-------------------------------------------------------------------------
|    |   movie_title   |  recommendations                                                                                                                                                  |
+----+-----------------+-------------------------------------------------------------------------
|  1 | The Dark Knight | 8.1(The Lord of the Rings: The Return of the King); 
                       | 8(Inception); 8(The Lord of the Rings: The The Fellowship of the Ring); 
                       | 7.9(The Matrix); 7.9(The Lord of the Rings: The Two Towers); 
                       | 7.5(Batman Begins); 7.5(The Dark Knight Rises); 
                       | 7.3(Iron Man);  6.6(Captain America: The First Avenger);  6.6(Iron Man 2)

由此查询生成的：

SELECT
      tmdb_movies.movie_title
    , GROUP_CONCAT(DISTINCT concat(r.recommendations_vote_average,'(',r.recommendations_title,')') 
                   ORDER BY r.recommendations_vote_average DESC 
                   SEPARATOR '; ' 
                  ) as recommendations
FROM tmdb_movies 
LEFT JOIN recommendations r ON r.recommendations_tmdb_id=tmdb_movies.tmdb_id
Where tmdb_movies.tmdb_id=1
GROUP BY tmdb_movies.movie_title

更多信息。下面的查询反转表优先级，并且不使用group_concat

select
    m.movie_title, r.*
from recommendations r
left join tmdb_movies m  ON r.recommendations_tmdb_id=m.tmdb_id
;

结果：

+----+-----------------+-------------------------+-------------------------------------------------------+------------------------------+
|    |   movie_title   | recommendations_tmdb_id |                 recommendations_title                 | recommendations_vote_average |
+----+-----------------+-------------------------+-------------------------------------------------------+------------------------------+
|  1 | The Dark Knight |                       1 | The Dark Knight Rises                                 |                          7.5 |
|  2 | The Dark Knight |                       1 | Batman Begins                                         |                          7.5 |
|  3 | The Dark Knight |                       1 | Iron Man                                              |                          7.3 |
|  4 | The Dark Knight |                       1 | The Lord of the Rings: The Return of the King         |                          8.1 |
|  5 | The Dark Knight |                       1 | The Lord of the Rings: The The Fellowship of the Ring |                            8 |
|  6 | The Dark Knight |                       1 | The Lord of the Rings: The Two Towers                 |                          7.9 |
|  7 | The Dark Knight |                       1 | The Matrix                                            |                          7.9 |
|  8 | The Dark Knight |                       1 | Inception                                             |                            8 |
|  9 | The Dark Knight |                       1 | Iron Man 2                                            |                          6.6 |
| 10 | The Dark Knight |                       1 | Captain America: The First Avenger                    |                          6.6 |
+----+-----------------+-------------------------+-------------------------------------------------------+------------------------------+

抽样数据(应该有问题)：

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');