时间戳

首页 > 解决方案 > sql server returns duplicates

sql - sql server returns duplicates

问题描述

I have a query in which I am fetching results from the sql server based on a list of values. Now there are two ways I can do this with:

  1. execute query for one value at a time and fetch the top 1 result
  2. Use a where-in clause and send multiple values.

The problem with the first approach is that it takes too much time since there are over 17000 values in the list. and The problem with the second approach is that it returns duplicates since the column I'm using for comparison is not unique. But it reduces execution time significantly.

The query is something like this:

'''SELECT *
FROM dbo.my_table
where AccountID IN {}
ORDER BY dummy_column DESC'''.format(values)

How can I remove those duplicates using the second approach? Or is there a better way to do it?

标签： sqlsql-server

解决方案

样本数据和期望的结果会有所帮助。但是你可以用row_number(). 这是一个不使用子查询的简单方法：

SELECT TOP (1) WITH TIES t.*
FROM dbo.my_table t
WHERE AccountID IN {}
ORDER BY ROW_NUMBER() OVER (PARTITION BY AccountId ORDER BY dummy_column DESC);

注意：这不会以任何特定方式对结果进行排序。目前尚不清楚这实际上是一个要求（鉴于第一种方法没有任何此类排序）。

推荐阅读