MySQL 5.7根据不同的列返回表的所有列 [英] MySQL 5.7 return all columns of table based on distinct column

问题描述

我刚刚升级到MySQL 5.7，但不幸的是，GROUP BY的某些功能已消失.我想从movies表中选择所有电影，只要类型int的movies.id不重复即可.我先前在MySQL 5.6中的查询是:

I just upgraded to MySQL 5.7 and unfortunately for me, some of the functionality of GROUP BY is gone. I wanted to select all movies from my movies table with as long as the movies.id of type int is not a duplicate. My previous query in MySQL 5.6 was:

SELECT *
FROM movies
WHERE movies.title LIKE '%example%'
GROUP BY movies.id

如果我有两部具有相同ID的电影，它将仅显示一部电影，而不是该电影及其副本.

If I had two movies with the same id, it would only display one movie, instead of that movie and its duplicates.

当我升级到MySQL 5.7时，GROUP BY给了我错误，我被告知要使用ORDER BY.但是，此查询:

When I upgraded to MySQL 5.7, the GROUP BY gave me errors and I was instead told to use ORDER BY. However this query:

SELECT *
FROM movies
WHERE movies.title LIKE '%example%'
ORDER BY movies.id

是否返回重复的电影.因此，有没有一种方法可以将其过滤掉，并且仅在不是重复项的情况下才返回一行?

Does return duplicate movies. So, is there a way to filter this out, and only return a row if it isn't a duplicate?

例如，如果这是我的movies表:

For example if this is my movies table:

movies
==================
|  id  |   title  |
==================
| 1    | example  |
------------------
| 2    | example  |
------------------
| 1    | example  |
------------------

以下是每个查询的输出:

Here is the output of each query:

Previous query result (with MySQL 5.6)
=======
1 | example
2 | example

New query result (with MySQL 5.7 and ORDER BY)
=======
1 | example
1 | example
2 | example

我希望最终结果不包含重复项(因此结果应看起来像第一个查询结果).

I want the final result to contain no duplicates (so the result should look like the first query result).

我了解我有点在滥用MySQL处理GROUP BY的方式.不幸的是，我对MySQL没有太多的经验，并且从StackOverflow那里得到了答案.我只想返回表中不包含重复的id的所有列.

Edit 2: I understand I was sort of abusing the way MySQL handled GROUP BY. Unfortunately, I do not have much experience with MySQL and got that answer from StackOverflow. I would just like to return all columns in my table that do not contain duplicate ids.

推荐答案

我相信distinct关键字

SELECT distinct movies.*
FROM movies 
WHERE movies.title = 'example'

这篇关于MySQL 5.7根据不同的列返回表的所有列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！