MySQL:每月为具有日期范围的数据创建一行

Question

!修改!

我有一个包含日期范围值的表 (mySQL 5.0.x)。

| id | link_id | type_id | value |  start | end    |
====================================================
|  1 |       1 |       1 |    10 | 201111 | 201202 |
|  2 |       1 |       2 |    20 | 201110 | 201201 |
|  3 |       1 |       1 |   100 | 201202 | 201202 |
|  4 |       2 |       1 |    40 | 201202 | 201203 |

在哪里

• id是记录的id
• link_id 用于将数据链接到其他表
• type_id用于判断其他表的值的类型
• value是数值
• start和end定义范围(实际日期或年月整数)

诀窍是我需要按月显示给定时间段内每种类型和链接的总值(value)。所以在 201201 - 201202 范围内的结果应该是这样的:

| period | link_id | type_id | value |
======================================
| 201201 |       1 |       1 |    10 |
| 201202 |       1 |       1 |   110 |
| 201201 |       1 |       2 |    20 |
| 201201 |       2 |    NULL |  NULL |
| 201202 |       2 |       1 |    40 |

我可以使用 PHP 将这样的信息放入数据库，但是有一些缺点。我有数百个 link_id，许多 type_id，开始日期和结束日期之间的平均差异是 30 个月，所以我会有大量的行。

Answer 1

这是一种方法。我已经输入了 $startMonth 和 $endMonth 这样你就可以看到它们是如何发挥作用的。(我有这种琐碎的感觉这可以简化，但它目前让我逃脱了) :

SET @MONTH:=$startMonth-1;

SELECT month, link_id, type_id, SUM(value) AS value
FROM (SELECT @MONTH:=@MONTH+1 as month
      FROM foo f
      LIMIT $endMonth-$startMonth+1) a
LEFT JOIN foo f
 ON a.month >= f.start AND a.month <= f.end
GROUP BY link_id, type_id, a.month;

它的工作方式:假设你有一个表a:

+-------+
| month |
+-------+
|201201 |
|201202 |
+-------+

那么你所追求的查询就更容易解决了:

SELECT month, link_id, type_id, SUM(value) AS value
FROM a
LEFT JOIN foo
 ON a.month >= f.start AND a.month <= f.end
GROUP BY link_id, type_id, a.month;

也就是说，通过确保 month 在开始和结束期间之间，您将表 a 连接到 foo，然后您只需总结值，按月份、链接 ID 和类型 ID 分组。

所以问题是如何生成表a，其中包含从$startMonth 到$endMonth 的数字。因此:

SET @MONTH:=201200;
SELECT @MONTH:=@MONTH+1 AS month
FROM foo
LIMIT 2;

这给出了一个数字表，从 201201 开始，有 2 行，即到 201202。在此查询中，FROM foo 是虚拟的 - 并未真正使用。