SQL查询返回表中每个组合的一条记录 [英] Sql query to return one single record per each combination in a table

问题描述

我需要具有（from_id，to_id）和最小值的循环的每个组合的结果。

I need the result for every combination of (from_id, to_id) which has the minimun value and the loop matching a criteria.

所以基本上我需要一个循环具有最小的价值。例如从A到B，我需要最小值和loop_id。

So basically I need the loop that has the minimun value. e.g. From A to B i need the minimun value and the loop_id .

该表具有以下字段：

value     from_id   to_id     loop_id
-------------------------------------

2.3         A         B          2

0.1         A         C          2

2.1         A         B          4

5.4         A         C          4

因此，结果将是：

value     from_id   to_id     loop_id
-------------------------------------

2.1         A         B          4

0.1         A         C          2

我尝试了以下操作：

SELECT t.value, t.from_id, t.to_id,t.loop_id
FROM myresults t 
INNER JOIN (
     SELECT min(m.value), m.from_id, m.to_id, m.loop_id
     FROM myresults m where m.loop_id % 2 = 0
     GROUP BY m.from_id, m.to_id, m.loop_id
) x
   ON  (x.from_id = t.from_id and x.to_id=t.to_id and x.loop_id=t.loop_id )
   AND x.from_id = t.from_id and x.to_id=t.to_id and x.loop_id=t.loop_id

但是它返回了所有循环。
预先感谢！

But it is returning all the loops. Thanks in advance!

推荐答案

据我所知，这将起作用：

As I understand the problem this will work:

SELECT  t.value, t.from_id, t.to_id, t.loop_id
FROM    MyResults t
        INNER JOIN
        (   SELECT  From_ID, To_ID, MIN(Value) [Value]
            FROM    MyResults
            WHERE   Loop_ID % 2 = 0
            GROUP BY From_ID, To_ID
        ) MinT
            ON MinT.From_ID = t.From_ID
            AND MinT.To_ID = t.To_ID
            AND MinT.Value = t.Value

但是，如果您有From_ID和To_ID组合的重复值，例如

However, if you had duplicate values for a From_ID and To_ID combination e.g.

value     from_id   to_id     loop_id
-------------------------------------

0.1         A         B          2

0.1         A         B          4

这将返回两行。

如果您使用的是SQL Server 2005或更高版本，您希望可以使用上述重复行：

If you are using SQL-Server 2005 or later and you want the duplicate rows as stated above you could use:

SELECT  Value, From_ID, To_ID, Loop_ID
FROM    (   SELECT  *, MIN(Value) OVER(PARTITION BY From_ID, To_ID) [MinValue]
            FROM    MyResults
        ) t
WHERE   Value = MinValue

如果您不希望重复的行，可以使用以下命令：

If you did not want the duplicate rows you could use this:

SELECT  Value, From_ID, To_ID, Loop_ID
FROM    (   SELECT  *, ROW_NUMBER() OVER(PARTITION BY From_ID, To_ID ORDER BY Value, Loop_ID) [RowNumber]
            FROM    MyResults
        ) t
WHERE   RowNumber = 1

这篇关于SQL查询返回表中每个组合的一条记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！