获取每月最小日期中最晚时间的所有记录的SQL查询修正请求

看起来你是想从数据集中筛选出每月1号的所有记录，但之前的SQL因为取了每月的最大日期（比如12月31号）导致结果不符合预期。我来帮你修正这个问题：

原始数据集 #

Date                Category  Amount
01/10/2014 20:04    2         12212
01/11/2014 0:00     3         11043.38
01/11/2014 16:03    2         12082
01/11/2014 16:32    3         110.43
01/12/2014 20:41    2         12196
01/12/2014 20:42    3         103.22
31/12/2014 14:20    2         12440
31/12/2014 14:21    3         104.25

期望结果 #

你需要保留每月1号的所有分类记录，排除非1号的条目：

Date                Category  Amount
01/10/2014 20:04    2         12212
01/11/2014 16:03    2         12082
01/11/2014 16:32    3         110.43
01/12/2014 20:41    2         12196
01/12/2014 20:42    3         103.22

原查询的问题 #

你之前的SQL通过分组取每个分类每月的最大日期，这会把每月最后一天的记录也选进来（比如12月31号的两条），不符合你的需求：

select t.date, t.Category, t.Amount 
from mytable t 
inner join ( 
    select Category,MONTH(date) MONTHH,YEAR(date) YEARR, max(date) as MaxDate 
    from mytable 
    group by Category,MONTH(date) MONTHH,YEAR(date) YEARR 
) tm on t.date = tm.MaxDate and t.Category = tm.Category

执行后错误结果：

Date                Category  Amount
01/10/2014 20:04    2         12212
01/11/2014 16:03    2         12082
01/11/2014 16:32    3         110.43
31/12/2014 14:20    2         12440
31/12/2014 14:21    3         104.25

修正后的SQL方案 #

核心思路是直接筛选日期中“日”部分为1的记录，不需要复杂的分组关联，简单高效：

通用版本（适配多数数据库） #

SELECT date, Category, Amount
FROM mytable
WHERE DAY(date) = 1;

分数据库适配版本 #

不同数据库的日期提取函数略有差异，这里给你几个常见数据库的写法：

• MySQL/MariaDB：用DAY()或DAYOFMONTH()都可以

  SELECT date, Category, Amount
  FROM mytable
  WHERE DAYOFMONTH(date) = 1;
  

• Oracle：使用EXTRACT函数

  SELECT date, Category, Amount
  FROM mytable
  WHERE EXTRACT(DAY FROM date) = 1;
  

• PostgreSQL：用EXTRACT或DATE_PART

  SELECT date, Category, Amount
  FROM mytable
  WHERE DATE_PART('day', date) = 1;
  

• SQL Server：直接用DAY()函数

  SELECT date, Category, Amount
  FROM mytable
  WHERE DAY(date) = 1;
  

为什么这个方案可行？ #

你的需求是固定保留每月1号的所有记录，直接筛选日期的日部分等于1，就能精准匹配到你要的条目，避免了原查询中取最大日期带来的错误。

内容的提问来源于stack exchange，提问作者yas real