MySQL 查询：从数据库中选择现有月份并动态完成该月份的其余月份

Question

我有两个表：

CREATE TABLE 'sales_sheet' (
  `_id` int(11) NOT NULL AUTO_INCREMENT,
  `_typed_by` int(11) DEFAULT NULL,
  `_product_id` int(11) DEFAULT NULL,
  `_year` date DEFAULT NULL,
  `_validation_state` int(11) DEFAULT NULL,
  PRIMARY KEY  (`_id`),
  KEY `_product_id` (`_product_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE 'sales_sheet_entries' (
  `_id` int(11) NOT NULL AUTO_INCREMENT,
  `_sheet_id` int(11) DEFAULT NULL,
  `_month` date DEFAULT NULL,
  `_quantity` int(11) DEFAULT NULL,
  PRIMARY KEY  (`_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

这两个表用于存储年销售额表。第一个表存储产品标识符、年份和工作表的验证状态。第二个存储产品的每月销售额。

我希望在单个查询中得到以下结果：

---------------------------------------------
| Month         | Sales volume  |
---------------------------------------------
| January 2010      |       |
| February 2010     | XXXXXX    |
| March 2010        | XXXXXX    |
| April 2010        | XXXXXX    |
| May 2010          |       |
| June 2010         |       |
| July 2010         |       |
| August 2010       |       |
| September 2010        |       |
| October 2010      | XXXXXX    |
| November 2010     | XXXXXX    |
| December 2010     |       |
---------------------------------------------

销售量列中的空字段可能对应于已保存在 sales_sheet_entries 表中的记录，但没有相应的销售量或在数据库中根本不存在，但查询必须显示它。

我的限制之一是我不能将 12 列直接对应于 sales_sheet 表中的月份列表，因为客户规格正在变化，他可能要求按期间而不是按期间填写销售额月。

我希望我说得足够清楚，提前感谢您的帮助和帮助。对不起我的英语。

Answer 1

MySQL 没有递归功能，因此您只能使用 NUMBERS 表技巧 -

1. 创建一个仅保存递增数字的表 - 使用 auto_increment 很容易做到：

   如果存在`example`则删除表。`numbers`；
   创建表“示例”。“数字”(
     `id` int(10) 无符号 NOT NULL 自动增量，
      主键（`id`）
   ) 引擎=InnoDB 默认字符集=latin1;
   

2. 使用以下方式填充表：

   插入数字
     （ID）
   价值观
     （无效的）
   

   ...您需要的任意数量的值。

3. 使用DATE_ADD< /a> 构建时间列表，根据 NUMBERS.id 值增加月份：

   <前><代码>选择 x.*
   FROM (SELECT DATE_ADD('2010-01-01', INTERVAL n.id - 1 个月)
   来自数字 n) x
   

4. 根据日期时间部分 LEFT JOIN 到数据表：

    SELECT DATE_FORMAT(x.dt, '%M %Y') AS 月份，
             y.sales_volume
        FROM (SELECT DATE_ADD('2010-01-01', INTERVAL n.id - 1 MONTH) AS dt
                来自数字 n) x
   左连接（选择 ss._product_id，
                     ss._year,
                     sse._月，
                     SUM(sse._quantity) AS 销售额
                来自 SALES_SHEET ss
                JOIN SALES_SHEET_ENTRIES sse ON sse._sheet_id = ss._id
             GROUP BY ss._product_id、ss._year、sse._month) y ON y._month = MONTH(x.dt)
                                                             AND y._year = YEAR(x.dt)
                                                             和 y._product_id = ?