不使用子查询的高级分组

2021-09-10 00:00:00 sql tsql sql-server

这是我拥有的数据样本.

This is a sample of the data that I have.

-ID-        -Rank-      -Type-      -Status-    -Amount-
1142474     2       Under Offer Approved    23
1148492     1       Present     Current     56
1148492     2       Under Offer Approved    3
2273605     1       Present     Current     24

ID 相同的地方我只想要排名最高的记录.所以查询的最终结果.

Where the ID is the same I only want the record with the highest rank. So the end result of the query.

-ID-        -Rank-      -Type-      -Status-    -Amount-
1142474     2       Under Offer Approved    23
1148492     1       Present     Current     56
2273605     1       Present     Current     24

现在获取原始数据集是一个昂贵的操作，所以我不想想要做一个按 ID 分组和然后分钟排名，然后然后重新连接到数据集上.因此，查询需要以另一种方式工作.

Now to get the original data set is an expensive operation, so I don't want to do a group by the ID and then mins the rank and then joins back onto the dataset again. Hence the query needs to do its work another way.

干杯安东尼

推荐答案

select t1.id
       , t1.rank
       , t1.type
       , t1.status
       , t1.amount

from   my_table t1 

       left outer join my_table as t2 
       on t1.id = t2.id 
    and 
       t2.rank < t1.rank 

where  t2.id is null

相关文章