将缺失的行添加到结果集中 [英] Add missing rows to a result set

问题描述

我有一个事实和暗表

create table #fact (SKey int, HT varchar(5), TitleId int)

insert into #fact values
(201707, 'HFI', 1),
(201707, 'HFI', 3),
(201707, 'HFI', 5),
(201707, 'HFI', 6),
(201707, 'REO', 1),
(201707, 'REO', 2),
(201707, 'REO', 4),
(201707, 'REO', 5)

create table #dim (TitleId int, Title varchar(10))
insert into #dim values
(1, 'UK'),
(2, 'AF'),
(3, 'LQ'),
(4, 'AL'),
(5, 'GT'),
(6, 'ML')

使用下面的查询

select #fact.SKey, #fact.HT, #fact.TitleId, #dim.Title
from #fact
    inner join #dim on #dim.TitleId = #fact.TitleId
order by #fact.SKey, #fact.HT, #fact.TitleId, #dim.Title

它返回我以下数据

   SKey    HT    TitleId   Title  
 -------- ----- --------- ------- 
  201707   HFI         1   UK     
  201707   HFI         3   LQ     
  201707   HFI         5   GT     
  201707   HFI         6   ML     
  201707   REO         1   UK     
  201707   REO         2   AF     
  201707   REO         4   AL     
  201707   REO         5   GT     

您会看到结果中缺少标题.例如，我的第一组(HFI"组)没有AF"和AL"，REO"部分没有LQ"和ML".

You see there are missing Titles in the result. for example, I don't have 'AF' and 'AL' for the first set ('HFI' set) and don't have 'LQ' and 'ML' for 'REO' part.

总而言之，我将生成以下结果

In summary I'm going to generate below result

   SKey    HT    TitleId   Title  
 -------- ----- --------- ------- 
  201707   HFI         1   UK     
  201707   HFI         2   AF     -- missing from first result
  201707   HFI         3   LQ     
  201707   HFI         4   AL     -- missing from first result
  201707   HFI         5   GT     
  201707   HFI         6   ML     
  201707   REO         1   UK     
  201707   REO         2   AF     
  201707   REO         3   LQ     -- missing from first result
  201707   REO         4   AL     
  201707   REO         5   GT     
  201707   REO         6   ML     -- missing from first result

目前我将第一个结果存储到临时表中，然后使用循环/游标将缺失的行添加到 int 中.

currently I'm store the first result into a temp table and then use a loop/cursor to add missing rows into int.

有没有什么办法可以只用一个查询来得到最终结果?

Is there any way we use just one query to get the final result?

推荐答案

可能是一个交叉连接，例如:

;with f as (
  select SKey, HT from fact
  group by SKey, HT
)
select f.SKey, f.HT, dim.TitleId, dim.Title 
from f, dim;

这是 sql fiddle.

这篇关于将缺失的行添加到结果集中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！