SQL 获取每个月数据集中的最大日期
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1786564/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL Get max date in dataset for each month
提问by Nick Vaccaro
I have a table with INT id and DATETIME date, amongst other fields. Rows are inserted into this table each weekday (not guaranteed), and several other tables use this id as a foreign key.
我有一个包含 INT id 和 DATETIME 日期的表，以及其他字段。每个工作日都会向这个表插入行（不保证），其他几个表使用这个 id 作为外键。
My question is, how can I get the id for the max date of each month, which I can then use to join to other data tables? For example, if the process ran today, I would want to see data for Jan 31, Feb 28, ... , Oct 31, Nov 23.
我的问题是，我怎样才能获得每个月最大日期的 id，然后我可以用它来加入其他数据表？例如，如果进程今天运行，我想查看 1 月 31 日、2 月 28 日、...、10 月 31 日、11 月 23 日的数据。
I am using SQL Server 2005.
我正在使用 SQL Server 2005。
回答by Aaron Bertrand
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;
回答by Joel
This will pull the month and year and last id:
这将拉取月份和年份以及最后一个 ID：
SELECT month(date), year(date), max(id)
FROM mytable
GROUP BY month(date), year(date)
And here's a test script
这是一个测试脚本
create table #mytable (
date datetime,
id int
)
insert into #mytable (date, id) values ('11/7/2009', 1)
insert into #mytable (date, id) values ('11/8/2009', 2)
insert into #mytable (date, id) values ('12/21/2009', 3)
insert into #mytable (date, id) values ('12/30/2009', 4)
insert into #mytable (date, id) values ('10/7/2009', 5)
insert into #mytable (date, id) values ('10/12/2009', 6)
SELECT month(date), year(date), max(id)
FROM #mytable
GROUP BY month(date), year(date)
drop table #mytable
回答by Joe Barone
I'd select back the maximum date using a group by query, like this:
我会使用 group by 查询来选择最大日期，如下所示：
Select Year(datetimefield) as MyYear, month(datetimefield) as MyMonth max(day(datetimefield)) as MaxDate
from table1
group by Year(datetimefield), month(datetimefield)
The query above will give you back the maximum transaction date for each month. To get the maximum Id associated with that date for each month, join the results of this back to the source table to get the max id for that day.
上面的查询将为您返回每个月的最大交易日期。要获取每个月与该日期关联的最大 ID，请将其结果连接回源表以获取当天的最大 ID。
So, your full query would look like this:
因此，您的完整查询如下所示：
select year(datetimefield) as MyYear, Month(datetimefield) as MyMonth, day(datetimefield), max(IdFieldName) as MaxID
from someTable inner join
(select year(datetimefield) as MyYear, Month(datetimefield) as MyMonth, max(day(datetimefield)) as MaxDate
from someTable
group by year(datetimefield), Month(datetimefield)) as innerSelect
on innerselect.MyYear = year(datetimefield) and
innerselect.MyMonth = Month(datetimefield) and
innerselect.MaxDate = day(datetimefield)
group by year(datetimefield), Month(datetimefield), day(datetimefield)
回答by lins314159
I'm assuming you only want the last day of the month that you have a record for (eg. Jan 30 if you've got nothing for Jan 31).
我假设您只想要您有记录的月份的最后一天（例如，如果您在 1 月 31 日什么都没有，则为 1 月 30 日）。
SELECT
id,
date
FROM (
SELECT
id,
date,
ROW_NUMBER() OVER (PARTITION BY YEAR(date), MONTH(date) ORDER BY DAY(date) DESC) AS rowNum
FROM sometable
) z
WHERE rowNum = 1;
