如何为MySQL中的每个ID只选择一行？

I have a MySQL table that holds many entries with repeated IDs (for various reasons) So you might have something like

我有一个MySQL表,其中包含许多具有重复ID的条目(由于各种原因)所以你可能有类似的东西

ID TIME DATA
1  xx   xx
2  xx   xx
3  xx   xx
1  xx   xx
3  xx   xx

What query can I run through PHP to select each ID only once? So I would like my result set to look like

我可以通过PHP运行什么查询来只选择一次ID?所以我希望我的结果集看起来像

ID TIME DATA
1  xx   xx
2  xx   xx
3  xx   xx

6 个解决方案

#1

Take a ganders at SELECT DISTINCT

选择SELECT DISTINCT的观众

#2

The suggestion given by @karim79 and @Matthew Jones, that DISTINCT(ID) can fix this problem, is a common misconception about the way DISTINCT works in SQL. It doesn't help, because DISTINCT always applies to the whole row, not a single column. The parentheses are irrelevant, as they would be in the queries SELECT (1) versus SELECT 1.

@ karim79和@Matthew Jones给出的建议DISTINCT(ID)可以解决这个问题,这是对DISTINCT在SQL中的工作方式的一种常见误解。它没有帮助,因为DISTINCT始终适用于整行,而不是单列。括号是无关紧要的,因为它们将在查询SELECT(1)与SELECT 1中。

The answer given by @T Pops is actually helpful in this case, because MySQL handles GROUP BY in a nonstandard way. See my answer to "Selecting a Column not in GROUP BY" for an explanation.

在这种情况下,@ T Pops给出的答案实际上是有用的,因为MySQL以非标准的方式处理GROUP BY。请参阅我的答案“选择不在GROUP BY中的列”作为解释。

Another solution is to use LEFT OUTER JOIN creatively to query for the first row per ID. For instance, assuming that the TIME column is unique for a given ID, you could do the following:

另一种解决方案是创造性地使用LEFT OUTER JOIN来查询每个ID的第一行。例如,假设TIME列对于给定ID是唯一的,您可以执行以下操作:

SELECT t1.*
FROM MyTable t1 LEFT OUTER JOIN MyTable t2
  ON (t1.ID = t2.ID AND t1.TIME > t2.TIME)
WHERE t2.ID IS NULL;

If t1 is pointing to the earliest row in the group, then there can be no matching row in t2 with an earlier date. Therefore, the LEFT OUTER JOIN will find no match, and leave t2.* as NULL.

如果t1指向组中最早的行,则t2中没有匹配的行与更早的日期。因此,LEFT OUTER JOIN将找不到匹配项,并将t2。*保留为NULL。

#3

It sounds like you're looking for GROUP BY.

听起来你正在寻找GROUP BY。

You can use a query similar to:

您可以使用类似于的查询:

SELECT id, time, data GROUP BY ID

You'll definitely have to do some tweaking to get that query to work with the structure you have but that's the basic idea.

您肯定需要做一些调整才能使该查询与您拥有的结构一起工作,但这是基本的想法。

Tizag has a great reference for using GROUP BY with MySQL and PHP.

Tizag对使用GROUP BY与MySQL和PHP有很好的参考。

#4

The SQL Query would look something like this:

SQL Query看起来像这样:

SELECT DISTINCT (ID), TIME, DATA FROM TableName

#5

Add a real primary key to the table, maybe called RealId.

向表中添加一个真正的主键,可能称为RealId。

Then you can:

然后你可以:

select *
from YourTable
where realid in (
    select min(realid)
    from YourTable
    group by id
)

Which would give you one row per id.

每个id会给你一行。

#6

SELECT * FROM MyTable GROUP BY ID ORDER BY ID ASC

#1