MySQL - 选择所有列，其中一列是 DISTINCT

MySQL - SELECT all columns WHERE one column is DISTINCT

提问by John Smith

I'm very sorry if the question seems too basic.
I've surfed entire Internet and StackOverflow for a finished solution, and did not find anything that I can understand, and can't write it myself, so have to ask it here.

如果问题看起来太基本，我很抱歉。
我已经在整个互联网和StackOverflow上浏览了一个完整的解决方案，并没有找到任何我能理解的东西，也不能自己写，所以不得不在这里问它。

I have a MySQL database.
It has a table named "posted".
It has 8 columns.

我有一个 MySQL 数据库。
它有一个名为“posted”的表。
它有 8 列。

I need to output this result:

我需要输出这个结果：

SELECT DISTINCT link FROM posted WHERE ad='$key' ORDER BY day, month

But I need not only the "link" column, but also other columns for this row.
Like for every row returned with this query I also need to know its "id" in the table, "day" and "month" values etc.

但我不仅需要“链接”列，还需要该行的其他列。
就像这个查询返回的每一行一样，我还需要知道它在表中的“id”、“day”和“month”值等。

Please tell me what should I read to make it, or how to make it.
Please keep it as simple as possible, as I'm not an expert in MySQL.

请告诉我我应该阅读什么来制作它，或者如何制作它。
请尽可能简单，因为我不是 MySQL 专家。

Edit: I tried this:

编辑：我试过这个：

SELECT DISTINCT link,id,day,month FROM posted WHERE ad='$key' ORDER BY day, month

It doesn't work. It returns too many rows. Say there are 10 rows with same links, but different day/month/id. This script will return all 10, and I want only the first one (for this link).

它不起作用。它返回太多行。假设有 10 行具有相同的链接，但日/月/id 不同。这个脚本将返回所有 10 个，我只想要第一个（对于这个链接）。

回答by DragonLord

The problem comes from instinctively believing that DISTINCTis a local pre-modifier for a column.

问题来自本能地认为这DISTINCT是列的本地预修饰符。

Hence, you "should"be able to type

因此，您“应该”能够输入

XXbadXX SELECT col1, DISTINCT col2 FROM mytable XXbadXX

XXbadXX SELECT col1, DISTINCT col2 FROM mytable XXbadXX

and have it return unique values for col2. Sadly, no.DISTINCTis actually a global post-modifier for SELECT, that is, as opposed to SELECT ALL(returning all answers) it is SELECT DISTINCT(returning all unique answers). So a single DISTINCTacts on ALL the columns that you give it.

并让它返回 2 的唯一值col。 遗憾的是，没有。DISTINCT实际上是 的全局后修饰符SELECT，也就是说，与SELECT ALL（返回所有答案）相反，它是SELECT DISTINCT（返回所有唯一答案）。因此，单个DISTINCT作用于您提供的所有列。

This makes it real hard to use DISTINCTon a single column, while getting the other columns, without doing major extremely ugly backflips.

这使得很难DISTINCT在单个列上使用，同时获得其他列，而无需进行极其丑陋的后空翻。

The correct answer is to use a GROUP BYon the columns that you want to have unique answers: SELECT col1, col2 FROM mytable GROUP BY col2will give you arbitrary unique col2rows, with their col1data as well.

正确的答案是GROUP BY在要获得唯一答案的列上使用 a ： SELECT col1, col2 FROM mytable GROUP BY col2将为您提供任意唯一的col2行，以及它们的col1数据。

回答by Bruno

I tried this:

SELECT DISTINCT link,id,day,month FROM posted
     WHERE ad='$key' ORDER BY day, month

It doesn't work. It returns too many rows. Say there are 10 rows with same links, but different day/month/id. This script will return all 10, and I want only the first one (for this link).

我试过这个：

SELECT DISTINCT link,id,day,month FROM posted
     WHERE ad='$key' ORDER BY day, month

它不起作用。它返回太多行。假设有 10 行具有相同的链接，但日/月/id 不同。这个脚本将返回所有 10 个，我只想要第一个（对于这个链接）。

What you're asking doesn't make sense.

你问的没有意义。

Either you want the distinct value of all of link, id, day, month, or you need to find a criterion to choose which of the values of id, day, monthyou want to use, if you just want at most one distinct value of link.

要么您想要所有 的不同值link, id, day, month，要么您需要找到一个标准来选择id, day, month要使用的值中的哪一个，如果您只想要 的最多一个不同值link。

Otherwise, what you're after is similar to MySQL's hidden columns in GROUP BY/HAVINGstatements, which is non-standard SQL, and can actually be quite confusing.

否则，您所追求的类似于MySQL 在GROUP BY/ HAVINGstatements 中的隐藏列，这是非标准 SQL，实际上可能会非常混乱。

You could in fact use a GROUP BY linkif it made sense to pick any row for a given linkvalue.

GROUP BY link如果为给定link值选择任何行有意义，则实际上可以使用 a 。

Alternatively, you could use a sub-select to pick the row with the minimal idfor a each linkvalue (as described in this answer):

或者，您可以使用子选择来选择id每个link值最小的行（如本答案所述）：

 SELECT link, id, day, month FROM posted
     WHERE (link, id) IN
           (SELECT link, MIN(id) FROM posted ad='$key' GROUP BY link)

回答by Joe Meyer

If what your asking is to only show rows that have 1 link for them then you can use the following:

如果您的要求是仅显示具有 1 个链接的行，那么您可以使用以下内容：

SELECT * FROM posted WHERE link NOT IN 
(SELECT link FROM posted GROUP BY link HAVING COUNT(LINK) > 1)

Again this is assuming that you want to cut out anything that has a duplicate link.

同样，这是假设您要删除任何具有重复链接的内容。

回答by Sinaesthetic

SELECT Id, Link, Day, Month FROM Posted
WHERE Id IN(
   SELECT Min(Id) FROM Posted GROUP BY Link)

回答by Walker Farrow

I think the best solution would be to do a subquery and then join that to the table. The sub query would return the primary key of the table. Here is an example:

我认为最好的解决方案是做一个子查询，然后将其加入到表中。子查询将返回表的主键。下面是一个例子：

select *
from (
          SELECT row_number() over(partition by link order by day, month) row_id
          , *

          FROM posted 
          WHERE ad='$key' 
          ) x
where x.row_id = 1

What this does is the row_number function puts a numerical sequence partitioned by each distinct link that results in the query.

这样做的作用是 row_number 函数放置一个数字序列，该序列由导致查询的每个不同链接分区。

By taking only those row_numbers that = 1, then you only return 1 row for each link.

通过只取那些 = 1 的 row_numbers，那么你只为每个链接返回 1 行。

The way you change what link gets marked "1" is through the order-by clause in the row_number function.

更改标记为“1”的链接的方式是通过 row_number 函数中的 order-by 子句。

Hope this helps.

希望这可以帮助。

回答by Failon

What you want is the following:

你想要的是以下内容：

SELECT DISTINCT * FROM posted WHERE ad='$key' GROUP BY link ORDER BY day, month

if there are 4 rows for example where link is the same, it will pick only one (I asume the first one).

例如，如果有 4 行链接相同，它将只选择一个（我假设第一个）。

回答by mlishn

SELECT DISTINCT link,id,day,month FROM posted WHERE ad='$key' ORDER BY day, month

OR

或者

SELECT link,id,day,month FROM posted WHERE ad='$key' ORDER BY day, month

回答by arunmoezhi

  SELECT OTHER_COLUMNS FROM posted WHERE link in (
  SELECT DISTINCT link FROM posted WHERE ad='$key' )
  ORDER BY day, month

回答by Zonata

If you want all columns where link is unique:

如果您想要链接唯一的所有列：

SELECT * FROM posted WHERE link in
     (SELECT link FROM posted WHERE ad='$key' GROUP BY link);

回答by Robert Sinclair

In MySQL yo can simply use "group by". Below will select ALL, with a DISTINCT "col"

在 MySQL 中，您可以简单地使用“group by”。下面将选择 ALL，带有 DISTINCT "col"

SELECT *
FROM tbl
GROUP BY col