MySQL获取一年中所有月份的数据[重复]

lsmepo6l 于 2023-01-16 发布在 Mysql

关注(0)|答案(1)|浏览(297)

- 此问题在此处已有答案**：

How to fill missing dates with time in range?（1个答案）
昨天关门了。
我需要获取月份的所有统计信息，但有些月份在表中没有记录。

SELECT `nationality` AS `nationality`,
MONTHNAME((booked_date)) AS `MonthName`,
COUNT(*) AS `dataCount`, 
MONTH(booked_date) AS `MonthNumber`, 
YEAR(booked_date) AS `YearNumber`,
SUM(amount) AS `amount`
FROM bookings
WHERE YEAR(booked_date) = '2023'
AND MONTH(booked_date) = '1'
GROUP BY `MonthName`,`nationality` 
ORDER BY `dataCount` DESC
LIMIT 1

我已经尝试过了，但我只得到了选定的月份，我需要得到该年的所有记录，即使该月份不存在于表中

CREATE TABLE `bookings` (
  `booking_id` int(11) NOT NULL,
  `full_name` varchar(255) NOT NULL,
  `birthYear` int(11) NOT NULL,
  `nationality` varchar(255) NOT NULL,
  `group` varchar(255) NOT NULL,
  `booked_by` int(11) DEFAULT NULL,
  `booked_date` date NOT NULL,
  `booked_on` date NOT NULL,
  `booking_ref` varchar(255) DEFAULT NULL,
  `amount` decimal(12,2) NOT NULL,
  `status` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

mysql

来源：https://stackoverflow.com/questions/75117018/mysql-get-all-the-months-of-the-year-data

1条答案

按热度按时间

s4n0splo1#

使用您提供的架构：

CREATE TABLE `bookings` (
  `booking_id` int(11) NOT NULL,
  `full_name` varchar(255) NOT NULL,
  `birthYear` int(11) NOT NULL,
  `nationality` varchar(255) NOT NULL,
  `group` varchar(255) NOT NULL,
  `booked_by` int(11) DEFAULT NULL,
  `booked_date` date NOT NULL,
  `booked_on` date NOT NULL,
  `booking_ref` varchar(255) DEFAULT NULL,
  `amount` decimal(12,2) NOT NULL,
  `status` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

您可以使用CTE（公用表表达式）构建月份列表，然后在这些月份上使用LEFT JOIN：

WITH calendar AS (
    SELECT 1 AS month_number, 'January' AS month_name UNION SELECT 2, 'February' UNION SELECT 3, 'March' UNION SELECT 4, 'April' UNION SELECT 5, 'May' UNION SELECT 6, 'June' UNION SELECT 7, 'July' UNION SELECT 8, 'August' UNION SELECT 9, 'September' UNION SELECT 10, 'October' UNION SELECT 11, 'November' UNION SELECT 12, 'December'
)

SELECT
    calendar.month_name,
    calendar.month_number,
    nationality,
    COUNT(bookings.booking_id) AS totalBookings,
    SUM(amount) AS amount
FROM
    calendar
    LEFT JOIN bookings ON calendar.month_number = MONTH(bookings.booked_date)
        AND YEAR(bookings.booked_date) = '2023'
GROUP BY
    calendar.month_name,
    nationality
ORDER BY
    calendar.month_number ASC

输出如下所示：

month_name   | month_number | nationality | totalBookings | amount 
------------ | ------------ | ----------- | ------------- | --------
January      | 1            | USA         | 50            | 5000
February     | 2            | Canada      | 40            | 4000
March        | 3            | USA         | 30            | 3000
April        | 4            | UK          | 20            | 2000
May          | 5            | USA         | 10            | 1000
June         | 6            | Canada      | 0             | 0
July         | 7            | USA         | 0             | 0
August       | 8            | UK          | 0             | 0
September    | 9            | USA         | 0             | 0
October      | 10           | Canada      | 0             | 0
November     | 11           | USA         | 0             | 0
December     | 12           | UK          | 0             | 0

赞(0）回复(0）举报 2023-01-16

我来回答

MySQL获取一年中所有月份的数据[重复]

1条答案

相关问题

热门标签

最新问答