[Oracle][SQL] 搜尋月份區間的簡易方法

/* 每個設備的單月加總 */
SELECT TO_CHAR(METERDATE, 'yyyy/mm'), SUM(METER) FROM WATERMETER
WHERE USERNO = 'F9605018'
AND EXTRACT( MONTH FROM METERDATE ) IN ( 5,4,3,2 )
AND EXTRACT( YEAR FROM METERDATE ) IN ( 2017 )
AND DEVICE = 'drinking'
GROUP BY TO_CHAR(METERDATE, 'yyyy/mm')
ORDER BY 1;


/* 用戶單月平均值 */
SELECT * FROM WATERMETER
WHERE userno = 'F9605018'
AND EXTRACT( MONTH FROM METERDATE ) IN ( 5 )
AND EXTRACT( YEAR FROM METERDATE ) IN ( 2017 );

SELECT * FROM WATERMETER
WHERE userno = 'F9605018'
AND EXTRACT( MONTH FROM METERDATE ) IN ( 3, 4, 5 )
AND EXTRACT( YEAR FROM METERDATE ) IN ( 2017 )
ORDER BY METERDATE;