根据第二列的最大值从列中提取数据的查询

Question

我有一个表，有[Order]， [Yield]， [Scrap]， [OpAc]列。我需要根据[OpAc]的最大值来拉动产量。

<表类> 为 收益率废OpActbody><<tr>123414001012341400201234130103012341300401234125550123411015601235140010123513822012351380301235138040123513805012351371601235137070

你差点就成功了!

WITH Orders_By_OpAc_Desc AS (
SELECT
[Order],
[Yield].
ROW_NUMBER() OVER (PARTITION BY [Order] ORDER BY OpAc DESC) AS [rn],
FROM
SCRAP
)
SELECT [Order],
[Yield]
FROM
Orders_By_OpAc_Desc
WHERE
rn = 1

这里的技巧是ROW_NUMBER() OVER (PARTITION BY [Order] ORDER BY OpAc DESC) AS [rn]。在SQL中理解它可能会令人困惑，但当用文字表达时，它会更清晰一些。

该语句获取具有相同Order值(PARTITION BY [Order])的每组行，按OpAc降序排列每组，以便较高的OpAc值最终"在顶部";(ORDER BY OpAc DESC)，并将组中的每一行编号为"top";到"bottom"，从1 (ROW_NUMBER())开始

意思是，将这个数字设置为1的每一行对于OrderId具有最高的OpAc值。

将其包装到CTE中，然后只选择将此数字(rn)设置为1的行。Voi-la .

您肯定需要OVER (PARTITION BY)，但MAX()也是这里的一个选项。你想要这样的:

SELECT 
* 
FROM
(
SELECT 
t3.* 
, MAX(OpAc) OVER (PARTITION BY  [Order]) max1
FROM
SCRAP t3
) a
WHERE
a.Max1 = a.OpAc

forMAX()

根据您的SQL Server版本，版本和查询需求，您也可以使用FIRST_VALUE():

SELECT 
DISTINCT
t3.[Order],
FIRST_VALUE(Yield) OVER(PARTITION BY [Order] ORDER BY OpAc DESC) Yield
FROM
SCRAP t3

你就差那么一点。只是在ROW_NUMBER函数中缺少ORDER BY OpAc DESC。

SQL小提琴

MS SQL Server 2017 Schema Setup:

CREATE TABLE orders (
[Order] int null
, Yield int null
, Scrap int null
, OpAc int null
);
INSERT INTO orders ([Order], Yield, Scrap, OpAc)
VALUES (1234,140,0,10)
, (1234,140,0,20)
, (1234,130,10,30)
, (1234,130,0,40)
, (1234,125,5,50)
, (1234,110,15,60)
, (1235,140,0,10)
, (1235,138,2,20)
, (1235,138,0,30)
, (1235,138,0,40)
, (1235,138,0,50)
, (1235,137,1,60)
, (1235,137,0,70)
;

查询1:

WITH CTE1 AS (
SELECT *
, ROW_NUMBER() OVER(PARTITION BY [Order] ORDER BY OpAc DESC) as row_num
FROM orders
) 
SELECT *
FROM CTE1 as c
WHERE c.row_num = 1

结果:

| Order | Yield | Scrap | OpAc | row_num |
|-------|-------|-------|------|---------|
|  1234 |   110 |    15 |   60 |       1 |
|  1235 |   137 |     0 |   70 |       1 |