MySQL -显示月份和日期
MySQL -显示月份和日期
提问于 2023-03-17 20:22:35
MySQL版本5.7.29-日志
我的表(事件)有EventID、EventTypeID、StartDateTime、EndDateTime
我想提取月份的名称，然后提取当月记录的日期，并输出如下：“9月21日、22日、30日”。
每一行都应显示该月份发生事件的任何记录的输出。
January: 21, 22
March: 2, 3, 9
...
December: 3, 6, 9
...注:日期应按升序排列，月份应按顺序排列。此外，它应该基于EventTypeID (group )。
我尝试过搜索这个，但我需要帮助寻找帮助，其中包括每个月的每一天，那里有记录。
这是我尝试过的代码，但它并不接近我所需要的。
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在这里可能有用，但我不清楚如何集成它。
回答 1
Database Administration用户
回答已采纳
发布于 2023-03-17 22:04:15
您仍然不太清楚您想要实现什么，因为您的示例每个事件类型每个月只包含一个，但是这可能会让您进行如下操作：
select
EventTypeID
,mo
,group_concat(d order by d separator ', ')
from (
SELECT
EventTypeID,
month(StartDateTime) mo,
day(StartDateTime) d
FROM
`events`
WHERE
`events`.StartDateTime > now()
) t
Group by EventTypeID, mo
order by mo小提琴 (顺便说一下，您应该创建它)。
页面原文内容由Database Administration提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://dba.stackexchange.com/questions/324884
复制相关文章
相似问题
腾讯云开发者
