提问人：ob213 提问时间：6/2/2013 更新时间：6/2/2013 访问量：39
如何完成属性列表以与父级的属性相同
How to complete the list of attributes to be the same as parent's
问：
我有一个包含州和县法规的法规表：
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
答：
1赞
HABO
6/2/2013
#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;
评论