有这样一个问题:做一个抄表查询,资料表是每天记录一笔能耗累积值,如何做个查询上月最大值与本月最大值之差。原始记录以下
2017-5-1
100
2017-5-30 200
2017-6-1
1500
2017-6-31 2200
2017-7-31 4200
2017-8-31 6500
查询结果格式如下
月份
能耗
2017-6月 2000
2017-7月 2000
2017-8月 2300
方法一:
导入access,一步搞定MsgBox DMax("能耗", "表1", "DatePart('m', 日期) = '6'")-DMax("能耗", "表1", "DatePart('m', 日期) = '5'")'6月最大能耗与5月最大能耗的差,其余类推!
方法二:
select A.月份,B.当月最大-A.当月最大 as 差额 from
(SELECT Max(表1.数量) AS 当月最大, Month([日期]) AS 月份
FROM 表1
GROUP BY Month([日期])) AS A
left join(
SELECT Max(表1.数量) AS 当月最大, Month([日期])-1 AS 月份
FROM 表1
GROUP BY Month([日期])) AS B
on A.月份=B.月份
方法三:
select format(DateSerial(year(a.月份), Month(a.月份), 1),'yyyy-m月') as
日期,
max(a.能耗)-(select max(b.能耗) from 表1 as b where DateSerial(year(a.月份),
Month(a.月份), 1)=DateSerial(year(b.月份), 1+Month(b.月份), 1)) as 结果
from 表1 as
a
where format(a.月份,'yyyy-m')<>(select format(min(月份),'yyyy-m') from
表1) group by DateSerial(year(a.月份), Month(a.月份), 1)