mysql - 将 MySQL join 翻译成 SQL Server 语法？

Question

在 MySQL 中，我曾经能够执行以下操作:

SELECT Something1.ID, Something2.AverageValue FROM
(SELECT ID, Max(Date) FROM Table GROUP BY ID) Something1
LEFT JOIN
(SELECT ID, AverageValue FROM Table) Something2
ON Something1.ID = Something2.ID

所以我想做的是给我每个 ID 的最新平均值

SQL Server 中的语法似乎不同，并且不允许我执行嵌套的 SELECT 语句？

Answer 1

WITH Data AS
(
 SELECT ROW_NUMBER() OVER (PARTITION BY ID ORDER BY Date DESC) AS Row_Number,
 ID,
 AverageValue
 FROM Table
)
SELECT * FROM Data WHERE Row_Number = 1

这就是您在 SQL Server 中应该执行的方式。它使“每个组的最后/第一个/最大/最小/最伟大的n个”变得简单。

这里的

PARTITION BY 关键字等于您的 GROUP BY 语句，并且这里的 ORDER BY 将转换为 MAX > 你在 MySQL 中使用的聚合函数

使用它可以让你做一些奇特的事情，例如:

WITH Data AS
    (
     SELECT ROW_NUMBER() OVER (PARTITION BY ID ORDER BY Date DESC) AS Row_Number,
     ID,
     AverageValue
     FROM Table
    )

SELECT Newest.ID, 
Newest.AverageValue, 
(Newest.AverageValue - Next_Newest.AverageValue) AS AverageValue_Change,
Newest.Date
 FROM Data Newest
LEFT OUTER JOIN Data Next_Newest 
 ON Newest.Row_Number+1 = Next_Newest.Row_Number
AND Newest.ID = Next_Newest.ID 

WHERE Newest.Row_Number=1