需求:
查询2019-08到2019-09所有数据
解决方法
方式一:
select
CONCAT(YEAR(s1.Date),'-',RIGHT(MONTH(s1.Date)+100,2)) as Month,
SUM(f1.Payment) as TicketsRevenue,
COUNT(DISTINCT s1.ScheduleId) as FlightsAmount,
COUNT(f1.ReservationId) as TicketsAmount
FROM `schedule` as s1
left join flightreservation as f1 ON s1.ScheduleId = f1.ScheduleId
WHERE (s1.Date > '2019-08-01' and s1.Date < '2019-09-30') and s1.Status` = 'Confirmed'
GROUP BY YEAR(s1.Date),MONTH(s1.Date)
缺点:只能在mysql5.7中使用
CONCAT(YEAR(s1.Date),'-',RIGHT(MONTH(s1.Date)+100,2)) as Month
作用:
- YEAR(s1.Date):这部分代码返回字段 s1.Date 的年份部分。YEAR() 是一个SQL函数,用于提取日期> 字段的年份。
- MONTH(s1.Date)+100:这部分代码返回字段 s1.Date 的月份部分,并加上 100。这是为了确保即使
月份是一位数,也能保留两位数的格式。例如,如果月份是 5,加上 100 后得到 105。- RIGHT(MONTH(s1.Date)+100,2):这部分代码返回上一步结果的最右两位数,即提取月份的后两位 > 数。RIGHT() 是一个SQL函数,用于返回字符串的最右边的指定位数。
- CONCAT(YEAR(s1.Date),‘-’,RIGHT(MONTH(s1.Date)+100,2)):这部分代码将年份和月份拼接起 > 来, 中间用短横线分隔。CONCAT() 是一个SQL函数,用于将多个字符串拼接成一个字符串。
综合起来,这段代码的作用是将日期字段 s1.Date 的年份和月份拼接成一个表示月份的字符串,格式 > 为 YYYY-MM。例如,如果 s1.Date 的值是 2019-08-15,那么 Month 字段的值将是 2019-05。
方式二(推荐
):
SELECT DATE_FORMAT(s1.Date, '%Y-%m') as Month,SUM(f1.Payment) as TicketsRevenue,COUNT(DISTINCT s1.ScheduleId) as FlightsAmount,COUNT(f1.ReservationId) as TicketsAmount
FROM `schedule` as s1
LEFT JOIN flightreservation as f1 ON s1.ScheduleId = f1.ScheduleId
WHERE (s1.Date BETWEEN '2019-08-01' AND LAST_DAY('2019-09-01')) AND s1.Status = 'Confirmed'
GROUP BY DATE_FORMAT(s1.Date, '%Y-%m')
其中
DATE_FORMAT(s1.Date, '%Y-%m') as Month
表示查询年份-月份
DATE_FORMAT函数作用:获取月份和年份的组合字符串
LAST_DAY
函数表示返回当前 date 或 datetime 类型值对应月份的最后一天。
注意:
上面的Date都是表中的字段
方式三:
SELECT
DATE_FORMAT(s1.Date, '%Y-%m') as Month,
SUM(f1.Payment) as TicketsRevenue,
COUNT(DISTINCT s1.ScheduleId) as FlightsAmount,
COUNT(f1.ReservationId) as TicketsAmount
FROM
schedule as s1
LEFT JOIN flightreservation as f1 ON s1.ScheduleId = f1.ScheduleId
WHERE
(s1.Date > '2019-08-01'
AND s1.Date < '2019-09-30')
AND s1.Status = 'Confirmed'
GROUP BY
DATE_FORMAT(s1.Date, '%Y-%m')
扩展
统计当月所遇数据条数:
SELECT * FROM schedule WHERE `date` like '2022-03%'
这里为了便于区别就使用 date字段(表中)
附件
参考1:https://www.sunzhongwei.com/mysql-query-date-type-fields-in-a-month
参考2:MySQL8.0中的日期类数据及其函数