SQL，日历和主键 [英] SQL, Calendar and Primary key

问题描述

我目前在与SQL有关的问题上处于困境，希望在大家中能有所帮助。
这是我的问题：
我有一个事实表，其中DATE列，B和C列是键，而VALUE是事实值。
我也有一个参照日历表。
我想要的是，对于每个现有元组BC，以及日历的每个日期（即使该日期没有事实，也要检索一行）。

I'm currently stuck on a SQL related issue, and I hope I will find help among you. Here is my issue: I have a Fact table, with column DATE, B and C being the key, and VALUE being the fact value. I also have a Referential Calendar table. What I want is, for each existing tuple B-C, and for each Date of my calendar (even if there is no fact on this date, retrieve a line.

例如，假设我有：

Calendar (DD-MM-YYYY)
01-01-2015
02-01-2015
03-01-2015

和事实

Date       | B  | C  | VALUE
=============================
02-01-2015 | aa | xx | 10
02-01-2015 | aa | yy | 15
03-01-2015 | aa | xx | 10

我要：

01-01-2015 | aa | xx | 0
01-01-2015 | aa | yy | 0
02-01-2015 | aa | xx | 10
02-01-2015 | aa | yy | 15
03-01-2015 | aa | xx | 10
03-01-2015 | aa | yy | 0

如何有效地做到这一点？

How could I do that in an efficient way?

感谢您提供的任何帮助。

Thanks in advance for any help you can provide.

推荐答案

首先创建笛卡尔积日历日期带有 BACT中的 B 和 C 列的唯一值表

First create the Cartesian product calendar dates with unique values of B and C column in FACT table

然后外部联接``第一步用 fact`表获得结果结果

Then Outer join`` the result of first step withfact` table to get the result

尝试类似的东西

;WITH cte 
     AS (SELECT Ca.dates, C, B
         FROM   (SELECT DISTINCT C, B 
                 FROM   fact) A 
                CROSS JOIN calendar Ca) 
SELECT A.dates, 
       A.B, 
       A.C, 
       Isnull(F.value,0) as value
FROM   cte A 
       LEFT OUTER JOIN fact F 
                    ON A.dates = f.dates 
                       AND A.C = F.C 
                       AND A.B = F.B 

这篇关于SQL，日历和主键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！