SQLite中基于财年（7-1至次年6-30）查询最晚降雪日期的问题

需求背景 #

• 财年周期：每年7月1日至次年6月30日
• 核心需求：从DiaryData表中筛选snowDepth <> 0的记录，忽略年份，找出月日最晚的那条降雪记录
• 测试数据集：

• 预期结果：

原查询的问题分析 #

原查询返回NULL，核心错误如下：

1. WHERE子句逻辑完全错误：lastDepth <> 0 BETWEEN ... AND ...的写法违背SQL语法逻辑，BETWEEN需要左右两边是范围值，此处把snowDepth的筛选条件和范围条件混写，导致没有任何数据匹配，最终返回NULL。
2. MAX函数的比较逻辑不符合需求：MAX(strftime('%m-%d-%Y', Timestamp))会将年份纳入比较，无法实现“忽略年份只比月日”的目标。
3. 财年范围计算逻辑无效：基于每条记录的Timestamp计算财年起始的写法，无法正确筛选指定财年的数据集，逻辑不成立。

正确的SQL实现 #

基础版本（忽略年份，全表找最晚月日的降雪记录） #

SELECT 
  Timestamp,
  strftime('%m-%d-%Y', Timestamp) AS lastDate,
  snowDepth AS lastDepth
FROM DiaryData
WHERE snowDepth <> 0
ORDER BY strftime('%m-%d', Timestamp) DESC
LIMIT 1;

逻辑说明 #

• WHERE snowDepth <> 0：先筛选出有降雪的有效记录
• strftime('%m-%d', Timestamp)：提取日期的月日部分，以此作为排序依据
• ORDER BY ... DESC：按月日降序排列，月日最晚的记录会排在首位
• LIMIT 1：取出排序后的第一条记录，即为目标结果

结合财年范围的版本（可选） #

如果需要限定查询某一财年的记录（例如2023财年：2022-07-01至2023-06-30），可以添加财年时间范围筛选：

SELECT 
  Timestamp,
  strftime('%m-%d-%Y', Timestamp) AS lastDate,
  snowDepth AS lastDepth
FROM DiaryData
WHERE snowDepth <> 0
  AND Timestamp BETWEEN '2022-07-01' AND '2023-06-30'
ORDER BY strftime('%m-%d', Timestamp) DESC
LIMIT 1;

测试验证 #

针对提供的测试数据，执行基础版本SQL后，会返回预期结果：

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