MySQL -选择一个列不同的所有列。

I'm very sorry if the question seems too basic.
I've surfed entire Internet and * 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.

如果这个问题太简单的话，我很抱歉。我已经浏览了整个互联网和*，想找到一个完整的解决方案，但是没有找到任何我能理解的东西，也不能自己写，所以必须在这里问一下。

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

我有一个MySQL数据库。它有一个名为“post”的表。它有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.

但我不仅需要“link”列，还需要这一行的其他列。与此查询返回的每一行一样，我还需要知道表中的“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，而我只想要第一个(对于这个链接)。

11 个解决方案

#1

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

问题来自于本能地相信，DISTINCT是列的局部预修饰符。

Hence, you "should" be able to type

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

XXbadXX SELECT col1, DISTINCT col2 FROM mytable XXbadXX

XXbadXX选择col1，不同于mytable XXbadXX中的col2

and have it return unique values for col2. Sadly, no. DISTINCT is 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 DISTINCT acts on ALL the columns that you give it.

并让它返回col2的唯一值。很遗憾,没有。DISTINCT是SELECT的全局后修饰符，也就是说，与SELECT ALL(返回所有答案)相反，它是SELECT DISTINCT(返回所有唯一答案)。所以你给它的所有列上都有一个不同的作用。

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

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

The correct answer is to use a GROUP BY on the columns that you want to have unique answers: SELECT col1, col2 FROM mytable GROUP BY col2 will give you arbitrary unique col2 rows, with their col1 data as well.

正确的答案是在您希望拥有唯一答案的列上使用一个GROUP BY:选择col1, col2从mytable GROUP中选择col2, col2将为您提供任意唯一的col2行，以及它们的col1数据。

#2

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，而我只想要第一个(对于这个链接)。

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, month you want to use, if you just want at most one distinct value of link.

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

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

否则，您所使用的是类似于MySQL的隐藏列，它是由非标准SQL语句组成的，而且实际上可能非常混乱。

You could in fact use a GROUP BY link if it made sense to pick any row for a given link value.

实际上，如果为给定的链接值选择任何行是有意义的，那么可以使用GROUP BY link。

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

或者，您可以使用子select为每个链接值选择具有最小id的行(如本答案所述):

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

#3

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

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

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.

这是假设你想要删除任何有重复链接的东西。

#4

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

或

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

#5

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

#6

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，那么每个链接只返回一行。

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.

希望这个有帮助。

#7

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

#8

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

#9

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行，例如链接是相同的，它只会选择一个(我是第一个)。

#10

I had a similar problem, maybe that help someone, for example - table with 3 columns

我有一个类似的问题，可能是帮助某人，例如，表3列。

SELECT * FROM DataTable WHERE Data_text = 'test' GROUP BY Data_Name ORDER BY Data_Name ASC

或

SELECT Data_Id, Data_Text, Data_Name FROM DataTable WHERE Data_text = 'test' GROUP BY Data_Name ORDER BY Data_Name ASC

Two ways work for me.

我有两种方法。

#11

-1

Select the datecolumn of month so that u can get only one row per link, e.g.:

选择月份的数据集合，这样每个链接只能有一行，例如:

select link, min(datecolumn) from posted WHERE ad='$key' ORDER BY day, month

Good luck............

祝你好运............

或

u if you have date column as timestamp convert the format to date and perform distinct on link so that you can get distinct link values based on date instead datetime

u如果您有日期列作为时间戳，则将格式转换为日期，并在链接上执行不同的操作，以便您可以基于日期而不是datetime获得不同的链接值

#1