使用Oracle/SQL查找最大值 [英] finding maximum values using Oracle / SQL group by

问题描述

我需要为具有5个列的表编写查询，这些列分别为ID1，ID2，ID3，A和B.这里， ID1，ID2和ID3构成主键.

I need to write a query for a table with 5 columns which are ID1, ID2, ID3, A, and B. Here, ID1, ID2, and ID3 form the primary key.

对于每个ID1，找到最大值A.如果两个或多个A具有最大值，请选择唯一一个具有最大B的记录.最后显示具有相应ID2和ID3值的结果.

For each ID1, find maximum A. If two or more As have the maximum values, choose the only one record that has maximum B. Finally show the results with corresponding ID2 and ID3 values.

例如，对于下表

ID1 ID2 ID3   A  B
  1   2   3  10  5 
  1   3   4  30  4
  1   3   5  30  3
  2   2   3   5  1
  2   3   4   9  2
  2   3   5  11  3

查询应显示如下结果.

ID1 ID2 ID3  Max_A  
  1   3   4     30  
  2   3   5     11  

推荐答案

其要旨是

• 在每行中添加一个ROW_NUMBER
• 重新为每个ID1
编号
• ORDER从A和B的高到低.
• 将其放入CTE中，以便在rn
上轻松访问

• add a ROW_NUMBER to each row
• restart the number for each ID1
• ORDER from high to low on A and B.
• put it in a CTE for easy access on rn

SQL语句

;WITH q AS (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY ID1 ORDER BY A DESC, B DESC) AS rn
  FROM YourTable
)
SELECT *
FROM   q
WHERE  rn = 1

这篇关于使用Oracle/SQL查找最大值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！