如何使用 SQL 返回指定日期或最近日期的值 <指定日期? [英] How can you use SQL to return values for a specified date or closest date < specified date?

问题描述

我已经编写了一个 SQL 语句来返回基于日期参数的价格列表，但我发现在某些日期，价格缺失.我正在寻找一种方法来修改此语句以返回指定日期的价格，但如果该价格不可用，则返回指定日期之前可用的最新价格的价格.

I've written an SQL statement to return a list of prices based on a date parameter, but I am finding that on some dates, the price is missing. I am looking for a way to modify this statement to return the price on the date specified, but if that is not available return the price for the most recent price available before the date specified.

Select date, grp, id, price
From
  price_table
Where
  date In ('12/31/2009', '11/30/2009') And
  grp In ('Group1')

例如，在我希望能够重写上面的语句以返回下面的所有记录，显示所有记录的适当参数日期.假设这是包含每日价格的表格的子集，下面的值是所记录月份的最后价格.

For example, in the I would like to be able to re-write the statement above to return all of the records below, showing appropriate parameter dates for all records. Assume this is a subset of a table with daily prices and the values below are the last prices for the months noted.

12/31/2009 Group1 1111 100
12/31/2009 Group1 2222 99
12/29/2009 Group1 3333 98
11/30/2009 Group1 1111 100
11/28/2009 Group1 2222 99
11/30/2009 Group1 3333 98

更新:多亏了下面 srgerg 的一些帮助，我已经能够创建一个每次只适用于一个日期的语句，但我仍然想找到一种方法来将多个日期传递给查询.

UPDATE: Thanks to some help from srgerg below, I have been able to create a statement that works for one date at a time, but I would still like to find a way to pass multiple dates to the query.

Select p1.date, p1.grp, p1.id, p1.price
From
  price_table As p1 Inner Join
  (Select Max(p2.date) As maxdt, id
    From
      price_table As p2
    Where
      p2.date <= '12/31/2009'
    Group By
      p2.id) As p On p.maxdt = p1.date And p1.id = p.id
Where grp in ('Group1')

推荐答案

你可以试试这样的:

SELECT date, grp, id
    , (SELECT TOP 1 price
       FROM price_table P2
       WHERE P1.id = P2.id
       AND P1.grp = P2.grp
       AND P2.date <= P1.date
       ORDER BY P2.date DESC)
FROM price_table P1
WHERE P1.date IN ('12/31/2009', '11/30/2009')
AND P1.grp IN ('Group1')

编辑要获取每个月的最后一条记录、组和 ID，您可以试试这个:

Edit To get the last record for each month, group and id you could try this:

SELECT date, grp, id, price
FROM price_table P1
WHERE P1.date = (SELECT MAX(date)
                 FROM price_table P2
                 WHERE P1.grp = P2.grp
                 AND P1.id = P2.id
                 AND YEAR(P1.date) = YEAR(P2.date)
                 AND MONTH(P1.date) = MONTH(P2.date))
AND P1.grp In ('Group1')

这篇关于如何使用 SQL 返回指定日期或最近日期的值 <指定日期?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！