sql-server - SQL 查询 - 给定两个日期，重复一个表的记录

Question

我需要查询

我有两个表，TableA 和 TableB

表A如下：

ID	姓名
1	车
2	飞机
3	船
4	自行车
5	滑冰

我的 TableB 如下所示：

ID	我的约会	FID
1	29-08-2021	1
2	29-08-2021	2
3	29-08-2021	3
4	30-08-2021	1
5	30-08-2021	5

FID：是将 TableB 与 TableA 相关联的列。

因此，我需要显示 TableA 的每条记录，以及 TableB 的记录。

我知道如何用一个日期来做到这一点。有些像这样：

SELECT ID, NAME, MyDATE
FROM TableA
LEFT JOIN TableB ON FID = ID AND MyDate = SomeDate
ORDER BY ID

如果 SomeDate = '29-08-2021'

该查询应返回以下结果：

ID	姓名	我的约会
1	车	29-08-2021
2	飞机	29-08-2021
3	船	29-08-2021
4	自行车	无效的
5	滑冰	无效的

如果 SomeDate = '30-08-2021'，它应该显示如下

ID	姓名	我的约会
1	车	30-08-2021
2	飞机	无效的
3	船	无效的
4	自行车	无效的
5	滑冰	30-08-2021

所以，我需要的是给出两个日期，并打印该日期范围内的所有内容，TableA 的所有记录及其对应的 TableB 记录

因此，如果我将日期 28-08-2021 指定为 30-08-2021，结果应如下所示

ID	姓名	我的约会
1	车	无效的
2	飞机	无效的
3	船	无效的
4	自行车	无效的
5	滑冰	无效的
1	车	29-08-2021
2	飞机	29-08-2021
3	船	29-08-2021
4	自行车	无效的
5	滑冰	无效的
1	车	30-08-2021
2	飞机	无效的
3	船	无效的
4	自行车	无效的
5	滑冰	30-08-2021

我需要进行一个返回此结果集的查询。

前5条记录，是28-09-2021对应的记录，因为TableB中没有那个日期的记录，所以返回null。

因为有三个日期，所以应该将表 A 的所有记录重复 3 次。

我试着这样做

SELECT ID, NAME, MyDATE
FROM TableA
LEFT JOIN TableB ON FID = ID AND MyDate BETWEEN StartDate AND EndDate
ORDER BY ID

但这样做只会返回 5 条记录（前 3 条记录的日期为 29-08-2021，第二条记录的日期为 30-08-201）。

我需要知道如何执行返回我提供的结果集的查询。

标签： sql-server

Answer 1

您似乎想要做的是获取 TableA 的笛卡尔积，其中包含日期范围内的所有日期。然后在每一行上添加信息，以确定您的 TableB 中是否有相应的行。

您可以从一个简单的递归 SQL 开始，它会获取您的日期：

WITH cte_dates(theDates) 
AS (
    SELECT @startdate
   UNION ALL
    SELECT DATEADD(day, 1, thedates)
      FROM cte_dates
     WHERE thedates < @enddate
)
SELECT *
 FROM cte_dates;

这将导致：

日期
2021-08-28
2021-08-29
2021-08-30

变量声明如下：

DECLARE @startdate DATE;
DECLARE @enddate DATE;

SET @startdate = '2021-08-28';
SET @enddate = '2021-08-30';

然后将其与您的 TableA 一起使用WHERE 1 = 1以获取笛卡尔积：

WITH cte_dates(thedates) 
AS (
    SELECT @startdate
   UNION ALL
    SELECT DATEADD(day, 1, thedates)
      FROM cte_dates
     WHERE thedates < @enddate
)
SELECT *
 FROM TableA
 JOIN cte_dates ON (1 = 1)
;

这将为您提供 15 行，其中 TableA 中的每一行都有一个带有日期的额外列。TableA 中的每个原始行将在此结果集中产生 3 行，因为有 3 个日期。

现在您可以使用外部左连接来获取相应的 TableB 值：

WITH cte_dates(thedates) 
AS (
    SELECT @startdate
   UNION ALL
    SELECT DATEADD(day, 1, thedates)
      FROM cte_dates
     WHERE thedates < @enddate
)
SELECT ta.id, ta.name, tb.mydate
 FROM TableA      AS ta
 JOIN cte_dates   AS dat ON (1 = 1)
 LEFT JOIN TableB AS tb  ON tb.fid = ta.id
                        AND tb.mydate = dat.thedates
;

---

小提琴手