如何完成与父级属性列表相同的属性列表

Question

我有一个包含州和县法规的规章表：

    RegID   State   County  RegulationName Regulation   
    1       CA              weight         10 pounds    
    1       CA              distance       20 miles 
    2       CA      Orange  distance       22 miles

家长(州)有全州范围的法规，例如。重量和距离。

儿童(县)可能会改写一些规定。在这个例子中，奥兰治县用它自己的值22英里覆盖了州规定的“距离”。

表中没有列出奥兰治县的“体重”规定。因为这意味着奥兰治县使用家长的“权重”

要始终显示一个县一级的所有法规的完整列表，有什么方法？返回未在县一级覆盖的隐含的州级法规。例如：

    RegId   State   County  RegulationName Regulation
    1       CA              weight         10 pounds    
    1       CA              distance       20 miles
    2       CA      Orange  weight         10 pounds 
    2       CA      Orange  distance       22 miles

Answer 1

假设这是一个简单的案例，县法规取代了州法规，而不管涉及的值是什么：

declare @Regulations as Table ( RegulationId Int Identity, RegId Int, State VarChar(2),
  County VarChar(16), RegulationName VarChar(16), Regulation VarChar(16) );

insert into @Regulations ( RegId, State, County, RegulationName, Regulation ) values
  ( 1, 'CA', NULL, 'weight', '10 pounds' ),
  ( 1, 'CA', NULL, 'distance', '20 miles' ),
  ( 2, 'CA', 'Orange', 'distance', '22 miles' ),
  ( 3, 'NY', NULL, 'weight', '1 stone' ),
  ( 4, 'NY', 'NYC', 'weight', '16 grams' ),
  ( 5, 'ND', NULL, 'shoe size', '9E' );

select * from @Regulations;

-- Start with all of the explicitly stated regulations.
select RegId, State, County, RegulationName, Regulation, 'Explicit' as [Type]
  from @Regulations
union all
-- Then add all of the missing county level regulations.
select L.RegId, L.State, C.County, L.RegulationName, L.Regulation, 'Implicit'
  from @Regulations as L cross join
    ( select distinct County, State from @Regulations where County is not NULL ) as C left outer join
    @Regulations as R on R.State = L.State and R.County = C.County and R.RegulationName = L.RegulationName
  -- Where the regulation applies at the state level (L.County is NULL) and
  --   there is no matching county level row (R.County is NULL from the LEFT OUTER JOIN).
  where L.County is NULL and R.County is NULL and C.State = L.State
  order by State, County, RegulationName;