如何将每个独特记录（基于2列）与另一个表中的每条记录结合起来

Question

这有点棘手，但我会尽我所能。

说我有两个表：

Dates（DateColumn日期时间）

SomeData（ID1 INT，ID2 INT，SomeDate日期时间）

---

Dates始终包含下列日期：

1/1/2012 
1/2/2012 
1/3/2012 

---

SomeData包含类似这样的数据：

ID ID1 ID2 SomeDate 
1 1 4 1/1/2012 
2 1 4 1/2/2012 
3 2 3 1/1/2012 
4 2 3 1/3/2012 
6 5 1 1/2/2012 

---

现在，请注意如何在SomeData，一些行具有相同的ID1和ID2的组合。另请注意，其中有些人如何遗漏Dates表中的某些日期。我需要在Dates表中以某种方式加入每个组合的每个日期，以查找缺失的日期。

于是就加入，数据最终会看起来像这样：

ID  ID1 ID2 SomeDate 
1  1 4 1/1/2012 
2  1 4 1/2/2012 
NULL 1 4 1/3/2012 <-- missing from SomeData table 

3  2 3 1/1/2012 
NULL 2 3 1/2/2012 <-- missing from SomeData table 
4  2 3 1/3/2012 

NULL 5 1 1/1/2012 <-- missing from SomeData table 
6  5 1 1/2/2012 
NULL 5 1 1/3/2012 <-- missing from SomeData table 

通知了每个ID1 + ID2组合中的每个返回3条记录，使用在Dates表中所有的日期，并通知了每个“缺失”记录保持ID1 + ID2组合。任何想法如何我可以把它关闭？

在此先感谢！

Answer 1

CROSS JOIN是你的朋友在这里。这是一个完整的样例

DECLARE @Dates TABLE (DateColumn DATETIME) 
INSERT @Dates VALUES ('20120101'), ('20120102'), ('20120103') 

DECLARE @SomeData TABLE (ID INTEGER IDENTITY(1,1), ID1 INTEGER, ID2 INTEGER, 
    SomeDate DATETIME) 
INSERT @SomeData VALUES (1,4,'20120101'),(1,4,'20120102'),(2,3,'20120101'), 
    (2,3,'20120103'), (5,1,'20120102') 


SELECT sd2.ID, sd1.ID1, sd1.ID2, d.DateColumn 
FROM 
(
    SELECT ID1, ID2 
    FROM @SomeData 
    GROUP BY ID1, ID2 
) sd1 
    CROSS JOIN @Dates d 
    LEFT JOIN @SomeData sd2 ON sd1.ID1 = sd2.ID1 
      AND sd1.ID2 = sd2.ID2 
      AND sd2.SomeDate = d.DateColumn 

因此，要打破它：

1. “SD1”子查询得到一组不同的所有ID1和ID2组合
2. 然后，这是CROSS APPLY'd到日期表。这样做的结果是，对于每个ID1 + ID2组合，它将导致在日期表中返回每个日期的行
3. 最后的LEFT JOIN然后尝试匹配ID1 + ID2 +返回日期ID值。如果找不到匹配，则返回NULL作为ID;如果找到匹配，则返回ID。

Answer 2

SELECT 
    sd.ID, 
    ids.ID1, 
    ids.ID2, 
    d.DateColumn AS SomeDate 
FROM 
(
    SELECT DISTINCT ID1, ID2 
    FROM SomeData 
) ids CROSS JOIN 
Dates d LEFT JOIN 
SomeData sd 
    ON sd.ID1 = ids.ID1 
    AND sd.ID2 = ids.ID2 
    AND sd.SomeDate = d.DateColumn