SQL语法 - 分组依据...自定义聚合功能？

I've got a situation like this:

我有这样的情况：

Table: FunTable

表：FunTable

ItemKey     ItemName     ItemPriceEffectiveDate    ItemPrice
11          ItemA        6/6/2009                  $500
12          ItemA        6/15/2009                 $550
13          ItemA        9/9/2009                  $450
14          ItemB        3/9/2009                  $150
15          ItemB        9/9/2009                  $350

I need to do the following:

我需要做以下事情：

Select
  ItemName as ItemDescription,
  (SELECT THE PRICE WITH THE LATEST EFFECTIVE DATE FROM TODAY THROUGH THE PAST)
FROM
  FunTable
GROUP BY
  ItemName

The output should be this:

输出应该是这样的：

ItemA $550
ItemB $150

So, effective dates for prices can range from 5 years ago through 5 years from now. I want to select the price that is effective either today or in the past (not in the future! it's not effective yet). It's got to be the "most recent" effective price.

因此，价格的有效日期可以从5年前到5年不等。我想选择今天或过去有效的价格（不是在未来！它还没有效果）。它必须是“最近的”有效价格。

Any thoughts?

有什么想法吗？

4 个解决方案

#1

Something like this (don't have your names, but you get the idea) ...

像这样的东西（没有你的名字，但你明白了）......

WITH A (Row, Price) AS
(
  SELECT Row_Number() OVER (PARTITION BY Item ORDER BY Effective DESC) AS [RN],
         Price FROM Table2
  WHERE Effective <= GETDATE()
)
SELECT * FROM A WHERE A.Row = 1

#2

select ft.ItemName,
       price
from   (select   ItemName,
                 Max(ItemPriceEffectiveDate) as MaxEff
        from     FunTable
        where    ItemPriceEffectiveDate <= GETDATE()
        group by ItemName) as d,
       FunTable as ft
where  d.MaxEff = ft.ItemPriceEffectiveDate;

Edit: Changed the query to actually work, assuming the price does not change more than once a day.

编辑：假设价格每天变化不止一次，将查询更改为实际工作。

#3

I don't know the answer off the top of my head, but my guess is that you mean need to use a sub-query, which could make it a very expensive query.

我不知道我的答案，但我的猜测是你的意思是需要使用子查询，这可能使它成为一个非常昂贵的查询。

It may be easier to pull all the data in an use code to post-process it yourself.

在使用代码中提取所有数据以自行进行后处理可能更容易。

#4

should work if you do

如果你这样做应该工作

SELECT ItemName, MAX(ItemPriceEffectiveDate)
FROM FunTable
WHERE ItemPriceEffectiveDate < getDate()
GROUP BY ItemName

#1