V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
MySQL 5.5 Community Server
MySQL 5.6 Community Server
Percona Configuration Wizard
XtraBackup 搭建主从复制
Great Sites on MySQL
Percona
MySQL Performance Blog
Severalnines
推荐管理工具
Sequel Pro
phpMyAdmin
推荐书目
MySQL Cookbook
MySQL 相关项目
MariaDB
Drizzle
参考文档
http://mysql-python.sourceforge.net/MySQLdb.html
enzo1205
V2EX  ›  MySQL

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

  •  
  •   enzo1205 · 2022-03-04 20:14:04 +08:00 · 1024 次点击
    这是一个创建于 755 天前的主题,其中的信息可能已经有所发展或是发生改变。

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

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

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

    mineralsalt
        1
    mineralsalt  
       2022-03-04 20:32:33 +08:00
    按月统计记录数量

    SELECT
    from_unixtime( create_time, "%Y-%m" ) AS months,
    count( id ) AS counts
    FROM
    `table`
    GROUP BY
    months;
    actar
        2
    actar  
       2022-03-04 20:54:08 +08:00
    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
        3
    rabbbit  
       2022-03-04 21:42:07 +08:00
    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
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   我们的愿景   ·   实用小工具   ·   1184 人在线   最高记录 6543   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 26ms · UTC 23:09 · PVG 07:09 · LAX 16:09 · JFK 19:09
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.