mysql - 将一周内的查找转换为一个月？

Question

我有一个查找，每周获取和分组结果：

SELECT
COUNT(IF(YEARWEEK(Submitted, 1) = YEARWEEK(NOW(), 1), 1, null)) AS Bookings,
COUNT(IF(YEARWEEK(Submitted, 1) = YEARWEEK(NOW()- INTERVAL 1 WEEK, 1), 1, null)) AS LastWeekBookings
FROM Bookings

是否有替代 YEARWEEK 的月份可以获取年份和月份以及上个月来计算一个月的预订量？

标签： mysqlsql

Answer 1

您可以使用该函数LAST_DAY()来确定当前或上个月：

SELECT
  SUM(LAST_DAY(Submitted) = LAST_DAY(NOW()) AS Bookings,
  SUM(LAST_DAY(Submitted) = LAST_DAY(NOW()- INTERVAL 1 MONTH)) AS LastMonthBookings
FROM Bookings