sqlserver 获取时间字段 每月最后一天 分组(分区)最后一条的记录

-- 查找表tb_name中所有每月最后一天的记录
SELECT * FROM tb_name WHERE date = DATEADD(mm, DATEDIFF(mm, -1, dateCol), -1) 

 

1、DATEDIFF(mm, -1, dateCol)

定义：DATEDIFF() 函数返回两个日期之间的时间。

语法：DATEDIFF(datepart,startdate,enddate)

这里startdate位置0表示1990-01-31，-1表示1899-12-31，该函数表示时间列距离'1899-12-31'相差多少个月

为什么31号而不是01号呢？

-- 执行下语句，最后一个字段显示1
SELECT *,  DATEDIFF(day, -1, '1900-01-01') FROM tb_name 

-- 执行下语句，最后一个字段显示31
SELECT *,  DATEDIFF(day, -1, '1900-01-31') FROM tb_name 

所以可以肯定的是DATE函数使用数字表示的是31号

2、DATEADD(mm, DATEDIFF(mm, -1, dateCol), -1)

定义：DATEADD() 函数在日期中添加或减去指定的时间间隔。

语法：DATEADD(datepart,number,date)

前面已经说了-1表示1899-12-31，加上时间列距离'1899-12-31'相差的月数，即表示时间列当月的最后一天

3、分组(分区)最后一条记录，因为之前说到的语句如果要统计这个月为止，但未到月底自然没有数据，所以要统计每月最后一条数据。

-- 查找表tb_name中groupCol列分组orderCol列排序下每种分组的最后一条记录
SELECT * FROM (
　　SELECT ROW_NUMBER() OVER(PARTITION BY groupCol ORDER BY orderCol DESC) AS Row_Index, *
　　FROM tb_name) t
WHERE t.Row_Index=1

-- 实际应用：查找表tb_name中每月(yyyy-MM)分组最后一条记录
SELECT CONVERT(varchar(7), DATE, 120) AS month, * 
    FROM (
        SELECT ROW_NUMBER() OVER(PARTITION BY CONVERT(varchar(7), DATE, 120) ORDER BY date DESC) AS Row_Index, * 
        FROM tb_name) t
    WHERE t.Row_Index=1

简单解释下：

ROW_NUMBER( )  OVER   编号
partition by             分组（分区）
order  by                排序