选择具有最大值的记录 [英] Selecting a Record With MAX Value

问题描述

在 SQL Server 2008 中，我有一个表 CUSTOMERS，它有两列:

In SQL Server 2008 I have a table CUSTOMERS that has two columns as:

身份证，余额

如何编写选择余额最大的客户 ID 的查询，以最有效的方式"?

How can I write the query that selects the ID of the customer who has maximum balance, "in the most effective way"?

选项 1:ORDER BY BALANCE 和 SELECT TOP(1) --> 成本太高.

Option 1: ORDER BY BALANCE and SELECT TOP(1) --> costs too much.

选项 2:首先获取 MAX 金额，然后使用 where 子句 中的金额进行另一个查询 --> 成本太高，看起来不可靠.

Option 2: Firstly Get MAX amount, then make another query that uses the amount in where clause --> costs too much and not seem reliable.

推荐答案

如果您为每个 Customer 有多个记录并且正在寻找每个 Customer 的最新余额(假设它们是过时的记录)，这里有一个选项:

Here's an option if you have multiple records for each Customer and are looking for the latest balance for each (say they are dated records):

SELECT ID, BALANCE FROM (
    SELECT ROW_NUMBER() OVER (PARTITION BY ID ORDER BY DateModified DESC) as RowNum, ID, BALANCE
    FROM CUSTOMERS
) C
WHERE RowNum = 1

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