如何将日期连接起来并在月份下检索
如何将日期连接起来并在月份下检索
提问于 2020-01-08 08:28:20
我使用假日桌作为：
我想要检索1行，列为一月至十二月，数据作为每个月的所有假期。我试过
SELECT
(CASE WHEN MONTH(hd.holiday_date)=1 THEN GROUP_CONCAT(CONCAT(hd.holiday_date, '#', hd.holiday_name)) ELSE NULL END) as JAN,
(CASE WHEN MONTH(hd.holiday_date)=2 THEN GROUP_CONCAT(CONCAT(hd.holiday_date, '#', hd.holiday_name)) ELSE NULL END) as FEB,
(CASE WHEN MONTH(hd.holiday_date)=3 THEN GROUP_CONCAT(CONCAT(hd.holiday_date, '#', hd.holiday_name)) ELSE NULL END) as MAR,
(CASE WHEN MONTH(hd.holiday_date)=4 THEN GROUP_CONCAT(CONCAT(hd.holiday_date, '#', hd.holiday_name)) ELSE NULL END) as APR,
(CASE WHEN MONTH(hd.holiday_date)=5 THEN GROUP_CONCAT(CONCAT(hd.holiday_date, '#', hd.holiday_name)) ELSE NULL END) as MAY,
(CASE WHEN MONTH(hd.holiday_date)=6 THEN GROUP_CONCAT(CONCAT(hd.holiday_date, '#', hd.holiday_name)) ELSE NULL END) as JUN,
(CASE WHEN MONTH(hd.holiday_date)=7 THEN GROUP_CONCAT(CONCAT(hd.holiday_date, '#', hd.holiday_name)) ELSE NULL END) as JUL,
(CASE WHEN MONTH(hd.holiday_date)=8 THEN GROUP_CONCAT(CONCAT(hd.holiday_date, '#', hd.holiday_name)) ELSE NULL END) as AUG,
(CASE WHEN MONTH(hd.holiday_date)=9 THEN GROUP_CONCAT(CONCAT(hd.holiday_date, '#', hd.holiday_name)) ELSE NULL END) as SEP,
(CASE WHEN MONTH(hd.holiday_date)=10 THEN GROUP_CONCAT(CONCAT(hd.holiday_date, '#', hd.holiday_name)) ELSE NULL END) as OCT,
(CASE WHEN MONTH(hd.holiday_date)=11 THEN GROUP_CONCAT(CONCAT(hd.holiday_date, '#', hd.holiday_name)) ELSE NULL END) as NOV,
(CASE WHEN MONTH(hd.holiday_date)=12 THEN GROUP_CONCAT(CONCAT(hd.holiday_date, '#', hd.holiday_name)) ELSE NULL END) as DECE
FROM `holiday` `hd`
WHERE `year` = '2020'
GROUP BY MONTH(hd.holiday_date) 使用group获得结果，但多个行将重放为
不分组，返回单行，但不按适当月份计算。
请给我建议正确的方法。谢谢。
回答 2
Stack Overflow用户
回答已采纳
发布于 2020-01-08 09:13:39
我想情况正好相反：
SELECT GROUP_CONCAT(CASE WHEN MONTH(holiday_date)=3
THEN CONCAT_WS('#',holiday_date,holiday_name)
ELSE NULL END) AS 'March',
GROUP_CONCAT(CASE WHEN MONTH(holiday_date)=4
THEN CONCAT_WS('#',holiday_date,holiday_name)
ELSE NULL END) AS 'April'
FROM holiday
WHERE YEAR=2020
GROUP BY YEAR;这里的小提琴示例：https://www.db-fiddle.com/f/2DUDuiGM4T12Ah7hxuPTdB/1
Stack Overflow用户
发布于 2020-01-08 08:43:11
1)创建内部查询，将其按月分组
SELECT
MONTH(hd.holiday_date) as m,
GROUP_CONCAT(CONCAT(hd.holiday_date, '#', hd.holiday_name)) as name
FROM holiday as hd
GROUP BY MONTH(hd.holiday_date) 2)在外部查询中，执行以下操作
SELECT
MAX(case when m = 1 then name else "" end) as Jan
MAX(case when m = 2 then name else "" end) as Feb
FROM (inner)所以结果是
SELECT
MAX(case when m = 1 then name else "" end) as Jan,
MAX(case when m = 2 then name else "" end) as Feb
// etc
FROM (
SELECT
MONTH(hd.holiday_date) as m,
GROUP_CONCAT(CONCAT(hd.holiday_date, '#', hd.holiday_name)) as name
FROM holiday as hd
GROUP BY MONTH(hd.holiday_date)
) as grouped页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/59642004
复制相关文章
相似问题
腾讯云开发者
