最小值MySQL查询 [英] Minimum Value MySQL Query

问题描述

我有一个类似于下面的数据表:

I have a data table similar to below:

ID   A   B
10  5    blue
10  8    red
10  10  yellow
20  2    black
20  17  blue
30  7    red
30  12  green
30  50  black

基本上，我想编写一个mySQL查询来输出类似以下内容的内容:

Basically I want to write a mySQL query to output something like:

ID   A   B
10  5    blue
20  2    black
30  7    red

它仅给出"ID"的唯一值以及每个唯一"ID"的最小值"A". "B"只是行中随之而来的多余数据.

It gives only unique values of 'ID' and the minimum values of 'A' of each unique 'ID'. 'B' is just the extra data that goes along with it in the row.

我的查询应该是什么样的?

What should my query look like?

推荐答案

您可以使用子查询为每个id标识min(a)值，然后将其重新连接到表中:

You can use a subquery to identify the min(a) value for each id and then join that back to your table:

select *
from yourtable t1
inner join
(
  select min(A) A, id
  from yourtable
  group by id
) t2
  on t1.id = t2.id
  and t1.A = t2.A

请参见带有演示的SQL小提琴

结果是:

| ID | A |     B |
------------------
| 10 | 5 |  blue |
| 20 | 2 | black |
| 30 | 7 |   red |

这篇关于最小值MySQL查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！