sql - SQL获取数据集中每个月的最大日期

Question

除其他字段外，我还有一个带有INT ID和DATETIME日期的表。每个工作日(不保证)在此表中插入行，并且其他几个表也将此ID用作外键。

我的问题是，如何获取每个月的最大日期的ID，然后可以将其用于加入其他数据表？例如，如果该流程今天运行，我希望查看1月31日，2月28日，...，10月31日，11月23日的数据。

我正在使用SQL Server 2005。

Answer 1

CREATE TABLE #foo (id INT, d DATETIME);

INSERT #foo(id,d) SELECT 1, '20091101'
UNION ALL SELECT 2, '20091102'
UNION ALL SELECT 3, '20091006'
UNION ALL SELECT 4, '20091001'
UNION ALL SELECT 5, '20091002'
UNION ALL SELECT 6, '20090904';

SELECT d, id
FROM 
( 
  SELECT d, id, rn = ROW_NUMBER() OVER 
  (PARTITION BY DATEDIFF(MONTH, '20000101', d)
   ORDER BY d DESC)
  FROM #foo
) AS x
WHERE x.rn = 1
ORDER BY x.d;

DROP TABLE #foo;