sql - 如何获取给定日期范围内当天至少有一个事件的所有日期？

Question

我有一个 SQLite 数据库，其中包含一个带有开始日期和结束日期的表Events。我想获得一个Set(可以是一个没有重复的数组)日期，他们至少在那天有一个事件，比如说从 8 月 1 日到 8 月 20 日。

让我们看一些示例事件:

Event 1 from 02.08.2016 to 07.08.2016
Event 2 from 10.08.2016 to 12.08.2016
Event 3 from 11.08.2016 to 15.08.2016

Then the outcome should be: 
[02.08.2016, 03.08.2016, 04.08.2016, 05.08.2016, 06.08.2016, 07.08.2016, 10.08.2016, 11.08.2016, 12.08.2016, 13.08.2016, 14.08.2016, 15.08.2016]

where 11.08.2016 and 12.08.2016 are not repeating.

注意:我期待一个可以解决这个问题的答案仅使用 SQL 而不是结合一些编程代码。我需要在移动设备 (iOS) 上尝试优化用户体验和性能。

Answer 1

以下是使用递归 CTE 创建日期表并使用它来选择您需要的日期的示例:

WITH RECURSIVE
  cnt(dt) AS 
  (
     SELECT MIN(startDate) FROM Events
     UNION ALL
     SELECT date(dt,'+1 day') FROM cnt 
           WHERE dt < (SELECT MAX(endDate) FROM Events)
           LIMIT 10000
  )
SELECT DISTINCT dt 
FROM cnt
JOIN Events ON dt BETWEEN startDate AND endDate
ORDER BY dt;

SQL fiddle demo