MySQL - 创建比一个开始更多的结果(例如 : integers within an interval)

Question

我可以回退到带有 while 循环的过程(也许，我以前从未尝试过这样做)，但必须有更快的方法。

[退格的东西 - 我只显示描述]

CREATE TABLE  `table` (
  `ZoneId` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `VolumeId` int(10) unsigned NOT NULL,
  `TypeId` int(10) unsigned NOT NULL,
  `ExtraTypeInfo` int(10) unsigned NOT NULL,
  `Time` int(10) unsigned NOT NULL,
  `StartIndex` int(10) unsigned NOT NULL,
  `EndIndex` int(10) unsigned NOT NULL,
  `SectionId` int(10) unsigned NOT NULL,
  PRIMARY KEY (`ZoneId`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

StartIndex 和 EndIndex 表示一个范围，它表示从开始到结束的所有内容都具有此记录描述的属性(比如范围涵盖 300,000 这比 300,000 好行，项目也不需要 ID，它们的位置就是它们的 ID)

有些索引会被覆盖两次，例如范围 [1,20] 和 [1,1] with TypeId 0 表示1 已经(无论它表示什么)两次，2-20 包括一次，其余为 0 次。

我宁愿将工作转移到 MySQL 而不是阅读表格并保持手动计数，我(遗憾地)必须使用 PHP，如果我为每个条目使用一个数组或对象，它会占用太多内存，而且花费的时间太长如果我使用位域。

与其抛出一个查询并使用它，不如学习如何执行此操作，因此我希望解决方案具有三列:

section | index | count0 | count1

其中 count0 是 TypeId 0 覆盖索引的次数，count1 是 1。我已经要求这两者来防止解决方案将作为两个查询完成。 (在现场这将受给定的 VolumeId 的约束)

一段的长度当然是已知的

例子

假设我们有(为简单起见忽略ZoneId、ExtraTypeInfo 和Time)这个数据集:

VolId TypeId Start End Section
1     0      1     2   1
1     0      2     2   1
2     0      1     1   1
1     1      2     3   1
1     0      1     2   2

要输出(以VolId = 1为准)

sec ind c0 c1
1   1   1  0
1   2   2  1
1   3   1  1
1   4   0  0  <--notice this, we do know the length of sec 1 and 4 was covered by nothing
2   1   1  0
2   2   1  0
2   3   0  0
2   4   0  0
2   5   0  0

索引可能从 0 或 1 开始，它很容易更改并且尚未确定(从 1 开始对于应用程序来说是最自然的，0 仅对代码有帮助)

希望SQL能做到这一点。我将继续阅读，但我不确定该怎么做，正如我在顶部提到的那样，我正在考虑一个遍历所有部分的过程，该过程调用一个过程来生成从 1 到长度的列表，然后搜索每个那些并希望 MySQL 的优化器发现正在发生的事情。

Answer 1

进一步到我们的conversation ，我建议您创建一个包含所有可能索引值的永久性number_table:

CREATE TABLE number_table (ind SMALLINT UNSIGNED PRIMARY KEY)
SELECT bF.v|bE.v|bD.v|bC.v|bB.v|bA.v|b9.v|b8.v
      |b7.v|b6.v|b5.v|b4.v|b3.v|b2.v|b1.v|b0.v ind
FROM
  (SELECT 0 v UNION ALL SELECT 1<<0x0) b0,
  (SELECT 0 v UNION ALL SELECT 1<<0x1) b1,
  (SELECT 0 v UNION ALL SELECT 1<<0x2) b2,
  (SELECT 0 v UNION ALL SELECT 1<<0x3) b3,
  (SELECT 0 v UNION ALL SELECT 1<<0x4) b4,
  (SELECT 0 v UNION ALL SELECT 1<<0x5) b5,
  (SELECT 0 v UNION ALL SELECT 1<<0x6) b6,
  (SELECT 0 v UNION ALL SELECT 1<<0x7) b7,
  (SELECT 0 v UNION ALL SELECT 1<<0x8) b8,
  (SELECT 0 v UNION ALL SELECT 1<<0x9) b9,
  (SELECT 0 v UNION ALL SELECT 1<<0xA) bA,
  (SELECT 0 v UNION ALL SELECT 1<<0xB) bB,
  (SELECT 0 v UNION ALL SELECT 1<<0xC) bC,
  (SELECT 0 v UNION ALL SELECT 1<<0xD) bD,
  (SELECT 0 v UNION ALL SELECT 1<<0xE) bE,
  (SELECT 0 v UNION ALL SELECT 1<<0xF) bF;
   
DELETE FROM number_table WHERE ind = 0;

然后你可以做一个outer join在此表和上面的 table 之间，通过相关部分的最大长度限制结果:

SELECT      s.SectionId,
            nt.ind,
            IFNULL(SUM(t.TypeID=0),0) c0,
            IFNULL(SUM(t.TypeID=1),0) c1
FROM        sections s
       JOIN number_table nt
         ON nt.ind <= s.Length
  LEFT JOIN `table` t
         ON t.SectionId = s.SectionId
        AND nt.ind BETWEEN t.StartIndex AND t.EndIndex
        AND t.VolumeId = 1
GROUP BY    s.SectionId, nt.ind

查看 sqlfiddle .