如何从表中选择多个不同的数据？(How to select multiple distinct data from a table?)

如何从表中选择多个不同的数据？(How to select multiple distinct data from a table?)

以下是我的表，我想选择具有唯一id1和最小差异的记录，我已经尝试过：

select * from table group by (id1) having min(difference)

但这只返回一条记录

| ID1 | ID2 | DIFFERENCE | |-----|-----|------------| | 1 | 1 | 1 | | 1 | 2 | 3 | | 1 | 3 | 4 | | 2 | 1 | 3 | | 2 | 3 | 4 |

现在我想选择具有最小差异的记录，即我想从表中获得第一行和第四行，因为它们具有最小差异和不同的id1。

Following is my table and I want to select the records having unique id1 and minimum difference, I have already tried this:

select * from table group by (id1) having min(difference)

but this returns only one record

| ID1 | ID2 | DIFFERENCE | |-----|-----|------------| | 1 | 1 | 1 | | 1 | 2 | 3 | | 1 | 3 | 4 | | 2 | 1 | 3 | | 2 | 3 | 4 |

Now I want to select the records having minimum difference i.e I want first row and 4th row from table because both having minimum difference and different id1.

最满意答案

尝试使用这样的子查询：

SELECT DISTINCT t1.* FROM Table1 t1 JOIN ( SELECT ID1, MIN(difference) AS MinDif FROM Table1 GROUP BY ID1 ) t2 ON t1.ID1 = t2.ID1 AND t1.difference = t2.MinDif;

如果您只想选择ID1和Difference列，那么即使不使用子查询也可以这样做：

SELECT ID1, MIN(difference) AS MinDif FROM Table1 GROUP BY ID1

看到这个SQLFiddle

Try using sub-query like this:

SELECT DISTINCT t1.* FROM Table1 t1 JOIN ( SELECT ID1, MIN(difference) AS MinDif FROM Table1 GROUP BY ID1 ) t2 ON t1.ID1 = t2.ID1 AND t1.difference = t2.MinDif;

If you want to select only ID1 and Difference columns then you can do this even without using sub-query:

SELECT ID1, MIN(difference) AS MinDif FROM Table1 GROUP BY ID1

See this SQLFiddle