SQL谜题(楼层谜题)

Multiple Dwellings
Baker, Cooper, Fletcher, Miller and Smith live on different floors of an apartment house that contains only five floors.
Baker does not live on the top floor. Cooper does not live on the bottom floor.
Fletcher does not live on either the top or the bottom floor. Miller lives on a higher floor than does Cooper.
Smith does not live on a floor adjacent to Fletcher’s.
Fletcher does not live on a floor adjacent to Cooper’s. Where does everyone live?

--Baker, Cooper, Fletcher, Miller and Smith住在一座房子的不同楼层.

--Baker 不住顶层.

--Cooper不住底层.

--Fletcher 既不住顶层也不住底层.
--Miller住得比Cooper高.
--Smith住的楼层和Fletcher不相邻.
--Fletcher住的楼层和Cooper不相邻.

 

select*from 
(select (number) Baker from master.dbo.spt_values where type='P' and number between 1 and 5 ) as a,
(select (number) Cooper from master.dbo.spt_values where type='P' and number between 1 and 5 ) as b,
(select (number) Fletcher from master.dbo.spt_values where type='P' and number between 1 and 5 ) as c ,
(select (number) Miller from master.dbo.spt_values where type='P' and number between 1 and 5 ) as d,
(select (number) Smith from master.dbo.spt_values where type='P' and number between 1 and 5 ) as e
where 
Baker<>5 and 
Cooper<>1 and 
Fletcher not in (1,5) and 
Miller>Cooper and 
abs(Smith-Fletcher)<>1 and 
abs(Fletcher-Cooper)<>1
and Baker<>Cooper 
and Baker<>Fletcher
and Baker<>Miller 
and Baker<>Smith
and Cooper<>Fletcher
and Cooper<>Miller
and Cooper<>Smith 
and Fletcher<>Miller
and Fletcher<>Smith
and Smith<>Miller

查询结果