试图围绕可怕的SQL查询

Can someone please help me understand what exactly this query does?

有人可以帮我理解这个查询究竟是做什么的吗?

SELECT pp.Sedol
    ,MAX(MAX(Id)) OVER (
        PARTITION BY pp.Sedol
        ,MAX(pp.ValueDate)
        ) PriceId
FROM Prices pp
GROUP BY pp.Sedol

2 个解决方案

#1

This is equivalent to:

这相当于:

with x as (
  select
    Sedol,
    max(id) max_id,
    Max(ValueDate) max_valuedate
  from
    Prices
  group by
    Sedol
) select
  Sedol,
  max(max_id) over (partition by Sedol, max_valuedate) PriceId
from
  x;

Although as Lamak says, I can't see any way this isn't going to just be equivalent to

虽然正如喇嘛所说的那样,我看不出任何方式这不等同于

SELECT Sedol, MAX(Id) PriceId FROM Prices GROUP BY Sedol

SQL Fiddle

#2

I think Lamak has already explained about the sql, I am posting an example for understanding. You can see both the sqls give same results. Copy paste below code in sql server and try:

我认为Lamak已经解释了sql,我发布了一个理解的例子。您可以看到两个sqls都给出相同的结果。在sql server中的代码下面复制粘贴并尝试:

declare @Prices table (Id int, ValueDate datetime, Sedol int)

Insert into @Prices values (1,'2014-09-06' ,200),
(2,'2014-09-07' , 100),
(3,'2014-09-08' , 100),
(4,'2014-09-09' , 100),
(5,'2014-09-10' , 300),
(6,'2014-09-11' , 300),
(7,'2014-09-12' , 100),
(8,'2014-09-13' , 200),
(9,'2014-09-14' , 200),
(10,'2014-09-15' , 200)

Select * from @Prices

-- Your SQL
SELECT pp.Sedol
    ,MAX(MAX(Id)) OVER (
        PARTITION BY pp.Sedol
        ,MAX(pp.ValueDate)
        ) PriceId
FROM @Prices pp
GROUP BY pp.Sedol

-- Simple SQL mentioned by Lamak
SELECT Sedol, MAX(Id) PriceId FROM @Prices GROUP BY Sedol

Resultset :