主页 / dba / 问题 / 303147
Accepted
Eric Lee
Asked: 2021-11-27 00:41:57 +0800 CST

MySQL：有没有办法根据日期动态生成多个日期范围

  • 772

我得到的数据是

ID 日期 cnt
1 '2021-11-26' 1
1 '2021-11-25' 1
1 '2021-11-24' 1
1 '2021-11-23' 1

我想要的结果是

ID 从日期 迄今为止 cnt
1 '2021-11-23' '2021-11-24' 2
1 '2021-11-23' '2021-11-25' 3
1 '2021-11-23' '2021-11-26' 4
1 '2021-11-24' '2021-11-25' 2
1 '2021-11-24' '2021-11-26' 3
1 '2021-11-25' '2021-11-26' 2

有没有办法从数据中的日期列生成这些日期（from_date，to_date）？

我需要从日期的迭代中使用循环吗？

我还能想到什么来接近结果？

提前致谢

mysql dynamic-sql

2 个回答

  1. Best Answer

    有没有办法生成这些日期（from_date，to_date）

    “三角连接”：

    SELECT a.date AS from_date,
       z.date AS to_date
    FROM t AS a
    CROSS JOIN t AS z
    WHERE a.date < z.date

    然后可以再次加入t以处理总和。

    • 2

  2. 假设一个简单的表结构是这样的：

    CREATE TABLE IF NOT EXISTS `Sample` (
    `id`            int(11)        UNSIGNED   NOT NULL    AUTO_INCREMENT,
    `date`          timestamp                 NOT NULL    ,
    `cnt`           int(11)        UNSIGNED   NOT NULL    DEFAULT 1,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

    使用这样的数据：

    INSERT INTO `Sample` (`date`)
VALUES ('2021-11-26'),
       ('2021-11-25'),
       ('2021-11-24'),
       ('2021-11-23');

    你可以这样做：

    SELECT tmp.`id`,
       DATE_FORMAT(tmp.`from_dts`, '%Y-%m-%d') as `from_date`,
       DATE_FORMAT(tmp.`to_dts`, '%Y-%m-%d') as `to_date`,
       SUM(sm.`cnt`) as `cnt`
  FROM `Sample` sm INNER JOIN (SELECT a.`id`, DATE_FORMAT(a.`date`, '%Y-%m-%d 00:00:00') as `from_dts`, DATE_FORMAT(z.`date`, '%Y-%m-%d 23:59:59') as `to_dts`
                                 FROM `Sample` a INNER JOIN `Sample` z ON a.`date` < z.`date`) tmp ON sm.`date` BETWEEN tmp.`from_dts` AND tmp.`to_dts`
 GROUP BY tmp.`id`, `from_date`, `to_date`
 ORDER BY `from_date`, `to_date`;

    当然还有其他方法可以实现同样的目标，但这将在紧要关头完成。但是，如果您要对数百万条记录运行这种查询，您将希望以非常不同的方式解决问题。

    • 0

相关问题