SQL查询最小最大

本文介绍了SQL查询最小最大的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我需要获取最大值和最小值，但我还需要在同一行上获取这些最大值或最小值的行ID。

I need to get maximum and minimum values but also I need to get row id of these maximum or minimum on the same row.

SELECT MIN([Value]), MAX([Value]), id FROM [AnalystEstimates].[dbo].[AnalystEstimateValues] GROUP BY indicatorid

推荐答案

目前尚不清楚您要从问题中得到什么。您真的要使用GROUP BY indicatorid吗？如果不是这样，那很简单，您已经有了很多答案。但是，如果您确实想使用GROUP BY，则难度会更大，而且还没有人完全正确。我还假设您每个指标id只需要一行，并且如果存在重复的行具有相同的最大/最小值，那么最好随意选择其中之一，而不要同时返回两者。

It's very unclear what you want from your question. Do you really want the GROUP BY indicatorid? If not then it's quite simple and you already have many answers. But if you do want to GROUP BY then it's more difficult and no-one has got it quite right yet. I also assume that you only want one row per indicatorid, and if there are duplicate rows that have the same max/min then it's better to just choose one of them arbitrarily instead of returning both.

这是我尝试使用CTE（需要SQL Server 2005或更高版本）：

Here's my attempt, using CTEs (requires SQL Server 2005 or newer):

WITH RowNumbers AS ( SELECT ROW_NUMBER() OVER (ORDER BY indicatorid, value) AS RowNumber, * FROM [AnalystEstimates].[dbo].[AnalystEstimateValues]), MinRowNumbers AS ( SELECT indicatorid, MIN(RowNumber) AS RowNumber FROM RowNumbers GROUP BY indicatorid), MaxRowNumbers AS ( SELECT indicatorid, MAX(RowNumber) AS RowNumber FROM RowNumbers GROUP BY indicatorid) SELECT MinRowNumbers.indicatorid, RN1.Value AS MinValue, RN1.ID AS MinValueId, RN2.Value AS MaxValue, RN2.ID AS MaxValueId FROM MinRowNumbers JOIN MaxRowNumbers ON MinRowNumbers.indicatorid = MaxRowNumbers.indicatorid JOIN RowNumbers RN1 ON MinRowNumbers.RowNumber = RN1.RowNumber JOIN RowNumbers RN2 ON MaxRowNumbers.RowNumber = RN2.RowNumber

以下是一些我用来测试的数据：

Here is some data I used to test it:

CREATE TABLE AnalystEstimateValues (ID int, indicatorid int, Value int); INSERT INTO AnalystEstimateValues (ID, indicatorid , Value) VALUES (1, 1, 4), (2, 1, 4), (3, 2, 6), (4, 1, 2), (5, 2, 2), (6, 2, 5), (7, 3, 0);

这是我得到的输出：

indicatorid MinValue MinValueId MaxValue MaxValueId 1 2 4 4 2 2 2 5 6 3 3 0 7 0 7

如果这不是您想要的，您能否尝试改善问题以告诉我们您想要什么？

If this isn't what you want, can you please try to improve your question to tell us what you do want?

更新：这是基于Craig Young的答案的替代解决方案，但使用联接而不是子选择：

Update: Here's an alternative solution based on Craig Young's answer but using joins instead of subselects:

WITH UniqueIds AS ( SELECT IndicatorId, Value, MIN(id) AS Id FROM AnalystEstimateValues GROUP BY IndicatorId, Value) SELECT lims.IndicatorId, MinValue, T1.Id AS MinValueId, MaxValue, T2.Id AS MaxValueId FROM ( SELECT IndicatorId, MIN(Value) as MinValue, MAX(Value) as MaxValue FROM AnalystEstimateValues GROUP BY IndicatorId) lims JOIN UniqueIds T1 ON lims.IndicatorId = T1.IndicatorId AND lims.MinValue = T1.Value JOIN UniqueIds T2 ON lims.IndicatorId = T2.IndicatorId AND lims.MaxValue = T2.Value

尽管我还没有运行性能测试来验证这一点，但是它比我的第一个版本更干净，也可能更快。

This is cleaner and probably also faster than my first version, although I haven't run performance tests to verify this.