MySQL 在递增日期时查询重复行 x 次

Question

我有一个管理水上运动中心类(class)的数据库。我有一个相对复杂的 SQL 查询，它将创建一个包含以下列的表。

“类(class)名称”、“开始日期”、“持续时间(天)”

例如。

 XYZ Sailing Course       12/01/17    3  
 ABC Windsurfing Course   13/01/17    2

我想创建一个 View /查询，其中每一天都有一行表示类(class)是类(class)。所以上面会显示

  XYZ Sailing Course       12/01/17 
  XYZ Sailing Course       13/01/17
  ABC Windsurfing Course   13/01/17     
  XYZ Sailing Course       14/01/17 
  ABC Windsurfing Course   14/01/17

幸运的是，如果一门类(class)在几天内同时进行，那么只有日期字段需要在其他重复的行中进行更改。此外，日子总是连续的，如果一个类(class)持续几天，中间就永远不会有空档。有没有一种优雅的方式来创建这个我没有想到的输出(甚至是一个困惑的方式 - 我被卡住了!)

非常感谢您提供的任何信息。

Answer 1

好吧，有点乱，但在@barmar的帮助下我解决了

我调整了原始查询中的行以提供“开始日期”和“结束日期”列，然后执行以下操作

SELECT 
        `a`.`Date` AS `Date`,
        Other rows required go here
    FROM
        ((SELECT 
            (CURDATE() - INTERVAL ((`a`.`a` + (10 * `b`.`a`)) + (100 * `c`.`a`)) DAY) AS `Date`
        FROM
            ((((SELECT 0 AS `a`) UNION ALL SELECT 1 AS `1` UNION ALL SELECT 2 AS `2` UNION ALL SELECT 3 AS `3` UNION ALL SELECT 4 AS `4` UNION ALL SELECT 5 AS `5` UNION ALL SELECT 6 AS `6` UNION ALL SELECT 7 AS `7` UNION ALL SELECT 8 AS `8` UNION ALL SELECT 9 AS `9`) `a`
        JOIN (SELECT 0 AS `a` UNION ALL SELECT 1 AS `1` UNION ALL SELECT 2 AS `2` UNION ALL SELECT 3 AS `3` UNION ALL SELECT 4 AS `4` UNION ALL SELECT 5 AS `5` UNION ALL SELECT 6 AS `6` UNION ALL SELECT 7 AS `7` UNION ALL SELECT 8 AS `8` UNION ALL SELECT 9 AS `9`) `b`)
        JOIN (SELECT 0 AS `a` UNION ALL SELECT 1 AS `1` UNION ALL SELECT 2 AS `2` UNION ALL SELECT 3 AS `3` UNION ALL SELECT 4 AS `4` UNION ALL SELECT 5 AS `5` UNION ALL SELECT 6 AS `6` UNION ALL SELECT 7 AS `7` UNION ALL SELECT 8 AS `8` UNION ALL SELECT 9 AS `9`) `c`)) `a`
        JOIN `MyDB`.`All Courses`)
    WHERE
        (`a`.`Date` BETWEEN `All Courses`.`Start Date` AND `All Courses`.`Finish Date`)

“所有类(class)”是包含所有其他行的 SQL View 。谢谢大家，一切都已修复!