MySQL 版本 5.7.29-日志
我的表(事件)有 EventID、EventTypeID、StartDateTime、EndDateTime
我想提取月份的名称,然后提取该月记录的日期,并输出如下:“September: 21, 22, 30”。
每行应显示该月有事件的任何记录的输出,例如
January: 21, 22
March: 2, 3, 9
...
December: 3, 6, 9
...
注:日期按升序排列,月份按顺序排列。而且,它应该基于EventTypeID(分组依据)。
我试过搜索这个,但我需要帮助来寻找包括一个月中每一天都存在记录的帮助。
这是我试过的代码,但它离我需要的还很远。
SELECT
eventid, EventTypeID,
IF (
DAY (startdatetime) = DAY(EndDateTime),
concat( DATE_FORMAT(StartDateTime, '%M'),': ', DAY (StartDateTime)),
concat( DATE_FORMAT(StartDateTime, '%M'),': ', DAY (StartDateTime), ' & ', DAY (EndDateTime) )
) as Days
FROM
`events`
WHERE
`events`.StartDateTime > now()
Group by EventTypeID, month(StartDateTime)
order by month(StartDateTime)
创建表:
CREATE TABLE `Events` (
`EventID` int NOT NULL ,
`EventTypeID` int NULL ,
`StartDateTime` datetime NULL ,
`EndDateTime` datetime NULL ,
PRIMARY KEY (`EventID`)
)
;
示例数据(25 行):
INSERT INTO `events` (`EventID`, `EventTypeID`, `StartDateTime`, `EndDateTime`) VALUES ('11139', '670', '2023-03-28 08:30:00', '2023-03-29 08:30:00');
INSERT INTO `events` (`EventID`, `EventTypeID`, `StartDateTime`, `EndDateTime`) VALUES ('10937', '1', '2023-03-21 08:00:00', '2023-03-21 14:00:00');
INSERT INTO `events` (`EventID`, `EventTypeID`, `StartDateTime`, `EndDateTime`) VALUES ('11161', '713', '2023-03-29 08:30:00', '2023-03-30 08:30:00');
INSERT INTO `events` (`EventID`, `EventTypeID`, `StartDateTime`, `EndDateTime`) VALUES ('10938', '64', '2023-03-21 08:00:00', '2023-03-21 09:00:00');
INSERT INTO `events` (`EventID`, `EventTypeID`, `StartDateTime`, `EndDateTime`) VALUES ('10969', '61', '2023-03-22 08:00:00', '2023-03-22 14:00:00');
INSERT INTO `events` (`EventID`, `EventTypeID`, `StartDateTime`, `EndDateTime`) VALUES ('10967', '27', '2023-03-23 09:00:00', '2023-03-23 15:00:00');
INSERT INTO `events` (`EventID`, `EventTypeID`, `StartDateTime`, `EndDateTime`) VALUES ('11093', '126', '2023-03-27 10:00:00', '2023-03-27 19:00:00');
INSERT INTO `events` (`EventID`, `EventTypeID`, `StartDateTime`, `EndDateTime`) VALUES ('11094', '710', '2023-03-27 10:00:00', '2023-03-27 18:00:00');
INSERT INTO `events` (`EventID`, `EventTypeID`, `StartDateTime`, `EndDateTime`) VALUES ('10970', '3', '2023-04-12 08:30:00', '2023-04-12 23:30:00');
INSERT INTO `events` (`EventID`, `EventTypeID`, `StartDateTime`, `EndDateTime`) VALUES ('10939', '1', '2023-04-26 08:30:00', '2023-04-26 14:30:00');
INSERT INTO `events` (`EventID`, `EventTypeID`, `StartDateTime`, `EndDateTime`) VALUES ('10972', '60', '2023-04-12 08:30:00', '2023-04-12 14:30:00');
INSERT INTO `events` (`EventID`, `EventTypeID`, `StartDateTime`, `EndDateTime`) VALUES ('10973', '61', '2023-04-14 08:00:00', '2023-04-14 14:00:00');
INSERT INTO `events` (`EventID`, `EventTypeID`, `StartDateTime`, `EndDateTime`) VALUES ('10917', '721', '2023-04-01 08:30:00', '2023-04-02 08:30:00');
INSERT INTO `events` (`EventID`, `EventTypeID`, `StartDateTime`, `EndDateTime`) VALUES ('11180', '21', '2023-04-15 10:00:00', '2023-04-15 18:30:00');
INSERT INTO `events` (`EventID`, `EventTypeID`, `StartDateTime`, `EndDateTime`) VALUES ('10953', '53', '2023-04-05 09:00:00', '2023-04-05 21:00:00');
INSERT INTO `events` (`EventID`, `EventTypeID`, `StartDateTime`, `EndDateTime`) VALUES ('11186', '673', '2023-04-19 10:00:00', '2023-04-19 15:00:00');
INSERT INTO `events` (`EventID`, `EventTypeID`, `StartDateTime`, `EndDateTime`) VALUES ('10954', '64', '2023-04-05 09:00:00', '2023-04-05 10:00:00');
INSERT INTO `events` (`EventID`, `EventTypeID`, `StartDateTime`, `EndDateTime`) VALUES ('11091', '126', '2023-04-22 10:00:00', '2023-04-22 19:00:00');
INSERT INTO `events` (`EventID`, `EventTypeID`, `StartDateTime`, `EndDateTime`) VALUES ('10901', '670', '2023-04-11 08:30:00', '2023-04-12 08:30:00');
INSERT INTO `events` (`EventID`, `EventTypeID`, `StartDateTime`, `EndDateTime`) VALUES ('11092', '710', '2023-04-22 10:00:00', '2023-04-22 18:00:00');
INSERT INTO `events` (`EventID`, `EventTypeID`, `StartDateTime`, `EndDateTime`) VALUES ('11130', '721', '2023-05-20 08:30:00', '2023-05-22 12:00:00');
INSERT INTO `events` (`EventID`, `EventTypeID`, `StartDateTime`, `EndDateTime`) VALUES ('11151', '670', '2023-05-21 08:00:00', '2023-05-22 08:00:00');
INSERT INTO `events` (`EventID`, `EventTypeID`, `StartDateTime`, `EndDateTime`) VALUES ('10941', '53', '2023-05-04 09:00:00', '2023-05-04 21:00:00');
INSERT INTO `events` (`EventID`, `EventTypeID`, `StartDateTime`, `EndDateTime`) VALUES ('10995', '27', '2023-05-23 09:00:00', '2023-05-23 15:00:00');
INSERT INTO `events` (`EventID`, `EventTypeID`, `StartDateTime`, `EndDateTime`) VALUES ('10942', '64', '2023-05-04 09:00:00', '2023-05-04 10:00:00');
INSERT INTO `events` (`EventID`, `EventTypeID`, `StartDateTime`, `EndDateTime`) VALUES ('10945', '1', '2023-05-26 08:00:00', '2023-05-26 14:00:00');
INSERT INTO `events` (`EventID`, `EventTypeID`, `StartDateTime`, `EndDateTime`) VALUES ('10977', '61', '2023-05-06 08:00:00', '2023-05-06 14:00:00');
INSERT INTO `events` (`EventID`, `EventTypeID`, `StartDateTime`, `EndDateTime`) VALUES ('10997', '3', '2023-05-30 08:00:00', '2023-05-30 23:00:00');
INSERT INTO `events` (`EventID`, `EventTypeID`, `StartDateTime`, `EndDateTime`) VALUES ('10892', '25', '2023-05-16 08:30:00', '2023-05-16 14:30:00');
INSERT INTO `events` (`EventID`, `EventTypeID`, `StartDateTime`, `EndDateTime`) VALUES ('10999', '60', '2023-05-30 08:00:00', '2023-05-30 14:00:00');
感觉group_concat
这里可能有用,但是不清楚怎么集成。
目前还不是很清楚你想要实现什么,因为你的样本每个月每个事件类型只包含一个,但也许这会让你继续:
fiddle(你应该顺便创建)。