MySQL-在其中一列是DISTINCT的情况下选择所有列 [英] MySQL - SELECT all columns WHERE one column is DISTINCT

问题描述

很抱歉，这个问题似乎太基本了.
我已经在整个Internet和StackOverflow上浏览了一个完整的解决方案，没有找到我能理解的东西，也无法自己写，所以必须在这里询问.

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.

我有一个MySQL数据库.
它有一个名为"posted"的表.
它有8列.

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

我需要输出以下结果:

I need to output this result:

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

但是我不仅需要链接"列，还需要该行的其他列.
像对于此查询返回的每一行一样，我还需要知道表中的"id"，"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.

请告诉我应该阅读什么，或者如何阅读.
请保持尽可能简单，因为我不是MySQL专家.

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.

我试过了:

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

它不起作用.它返回太多行.假设有10行具有相同的链接，但日期/月份/编号不同.该脚本将返回所有10个脚本，而我只想要第一个(对于此链接).

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

推荐答案

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

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

因此，您应该" 能够键入

XXbadXX SELECT col1, DISTINCT col2 FROM mytable XXbadXX

并使它返回col 2的唯一值. 遗憾的是，.DISTINCT实际上是SELECT的全局后置修饰符，也就是说，与SELECT ALL(返回所有答案)相对，它是SELECT DISTINCT(返回所有唯一答案) ).因此，单个DISTINCT会作用在您赋予它的所有列上.

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.

这使得在获取其他列的同时不进行主要极其难看的后空翻而很难在单个列上使用DISTINCT.

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

正确的答案是在要具有唯一答案的列上使用GROUP BY: SELECT col1, col2 FROM mytable GROUP BY col2 将为您提供任意唯一的col2行以及它们的col1数据也是

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.

这篇关于MySQL-在其中一列是DISTINCT的情况下选择所有列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！