白嫖大家的代码， Mysql 如何按月份调用，或是如何设计数据库...

[enzo1205]

最终的效果是要这样,以此类推

. . 2022 1 月 2 月 3 月 4 月 5 月 6 月 7 月 8 月 9 月 10 月 11 月 12 月 . . .

博客只有一个时间字段..我现在可以调出来，系统里面有的文章.. 比如 2022 1 月 2 月 3 月... 但是我想弄完整的月份，有文章话，该月份可以点击，没有文章的月份不能点击... 麻烦大家给个思路.... 谢谢

[mineralsalt]

按月统计记录数量

SELECT
from_unixtime( create_time, "%Y-%m" ) AS months,
count( id ) AS counts
FROM
`table`
GROUP BY
months;

[actar]

select concat('2022', '-', months.month) as time, count(articles.id) > 0 as exist
from (
select '01' as month
union
select '02' as month
union
select '03' as month
union
select '04' as month
union
select '04' as month
union
select '06' as month
union
select '07' as month
union
select '08' as month
union
select '09' as month
union
select '10' as month
union
select '11' as month
union
select '12' as month
) as months
left join articles on month(created_at) = months.month and year(articles.created_at) = '2022'
group by time;


随便写的，不保证任何效率

[rabbbit]

CREATE TABLE IF NOT EXISTS `blog` (
`id` int NOT NULL DEFAULT '0',
`title` varchar(100) NOT NULL DEFAULT '',
`createTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updateTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`content` mediumblob
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

SELECT from_unixtime(UNIX_TIMESTAMP(createTime), '%Y 年%m 月') TIME FROM blog GROUP BY time

SELECT
*,
from_unixtime(UNIX_TIMESTAMP(createTime), '%Y 年%m 月') TIME,
ROW_NUMBER() over (partition by from_unixtime(UNIX_TIMESTAMP(createTime), '%Y-%m') ORDER BY createTime) num
FROM blog