爱因斯坦的超级问题(谁养鱼)SQL解法
问题：
在一条街上 有5座房子 喷了5种颜色
每个房里住着不同国籍的人
每个人喝不同的饮料
抽不同的香烟
养不同的宠物
请问，谁养鱼？
提示：
A. 英国人住红色房子
B. 瑞典人养狗
C. 丹麦人喝茶
D. 绿色房子在白色房子左面
E. 绿色房子主人喝咖啡
F. 抽 Pall Mall 香烟的人养鸟
G. 黄色房子主人抽 Dunhill 香烟
H. 住在中间房子的人喝牛奶
I. 挪威人住第一间房
J. 抽 Blends 香烟的人住在养猫的人隔壁
K. 养马的人住抽 Dunhill 香烟的人隔壁
L. 抽 Blue Master 的人喝啤酒
M. 德国人抽 Prince 香烟
N. 挪威人住蓝色房子隔壁
O. 抽 Blends 香烟的人有一个喝水的邻居
--建立临时表
declare @t table(序号 int,房子 varchar(20),国籍 varchar(20),饮料 varchar(20),香烟 varchar(20),宠物 varchar(20))
--全部组合
insert @t select * from
(select 1 序号 union select 2 union select 3 union select 4 union select 5 ) n,
(select '红色' 房子 union select '绿色' union select '白色' union select '黄色' union select '蓝色' ) c,
(select '英国' 国籍 union select '瑞典' union select '丹麦' union select '挪威' union select '德国' ) g,
(select '茶' 饮料 union select '咖啡' union select '牛奶' union select '啤酒' union select '水' ) d,
(select 'Pall Mall' 香烟 union select 'Dunhill' union select 'Blends' union select 'Blue Master' union select 'Prince') s,
(select '狗' 宠物 union select '鸟' union select '猫' union select '马' union select '鱼' ) a
--按条件删除
delete @t where 国籍='英国' and 房子<>'红色' or 国籍<>'英国' and 房子='红色' -- A
delete @t where 国籍='瑞典' and 宠物<>'狗' or 国籍<>'瑞典' and 宠物='狗' -- B
delete @t where 国籍='丹麦' and 饮料<>'茶' or 国籍<>'丹麦' and 饮料='茶' -- C
delete @t where 房子='绿色' and 饮料<>'咖啡' or 房子<>'绿色' and 饮料='咖啡' -- E
delete @t where 香烟='Pall Mall' and 宠物<>'鸟' or 香烟<>'Pall Mall' and 宠物='鸟' -- F
delete @t where 香烟='Dunhill' and 房子<>'黄色' or 香烟<>'Dunhill' and 房子='黄色' -- G
delete @t where 序号=3 and 饮料<>'牛奶' or 序号<>3 and 饮料='牛奶' -- H
delete @t where 序号=1 and 国籍<>'挪威' or 序号<>1 and 国籍='挪威' -- I
delete @t where 香烟='Blue Master' and 饮料<>'啤酒' or 香烟<>'Blue Master' and 饮料='啤酒' -- L
delete @t where 香烟='Prince' and 国籍<>'德国' or 香烟<>'Prince' and 国籍='德国' -- M
delete @t where 序号=2 and 房子<>'蓝色' or 序号<>2 and 房子='蓝色' -- N & I
--筛选
select * from @t a,@t b,@t c,@t d,@t e
where a.序号=1 and b.序号=2 and c.序号=3 and d.序号=4 and e.序号=5
and a.国籍<>b.国籍 and a.香烟<>b.香烟 and a.饮料<>b.饮料 and a.房子<>b.房子 and a.宠物<>b.宠物
and a.国籍<>c.国籍 and a.香烟<>c.香烟 and a.饮料<>c.饮料 and a.房子<>c.房子 and a.宠物<>c.宠物
and a.国籍<>d.国籍 and a.香烟<>d.香烟 and a.饮料<>d.饮料 and a.房子<>d.房子 and a.宠物<>d.宠物
and a.国籍<>e.国籍 and a.香烟<>e.香烟 and a.饮料<>e.饮料 and a.房子<>e.房子 and a.宠物<>e.宠物
and b.国籍<>c.国籍 and b.香烟<>c.香烟 and b.饮料<>c.饮料 and b.房子<>c.房子 and b.宠物<>c.宠物
and b.国籍<>d.国籍 and b.香烟<>d.香烟 and b.饮料<>d.饮料 and b.房子<>d.房子 and b.宠物<>d.宠物
and b.国籍<>e.国籍 and b.香烟<>e.香烟 and b.饮料<>e.饮料 and b.房子<>e.房子 and b.宠物<>e.宠物
and c.国籍<>d.国籍 and c.香烟<>d.香烟 and c.饮料<>d.饮料 and c.房子<>d.房子 and c.宠物<>d.宠物
and c.国籍<>e.国籍 and c.香烟<>e.香烟 and c.饮料<>e.饮料 and c.房子<>e.房子 and c.宠物<>e.宠物
and d.国籍<>e.国籍 and d.香烟<>e.香烟 and d.饮料<>e.饮料 and d.房子<>e.房子 and d.宠物<>e.宠物
and (a.房子='绿色' and b.房子='白色' or b.房子='绿色' and c.房子='白色' -- D
or c.房子='绿色' and d.房子='白色' or d.房子='绿色' and e.房子='白色') -- D
and (b.宠物='猫' and (a.香烟='Blends' or c.香烟='Blends' ) or b.香烟='Blends' and (a.宠物='猫' or c.宠物='猫') -- J
or c.宠物='猫' and (b.香烟='Blends' or d.香烟='Blends' ) or c.香烟='Blends' and (b.宠物='猫' or d.宠物='猫') -- J
or d.宠物='猫' and (c.香烟='Blends' or e.香烟='Blends' ) or d.香烟='Blends' and (c.宠物='猫' or e.宠物='猫')) -- J
and (b.宠物='马' and (a.香烟='Dunhill' or c.香烟='Dunhill') or b.香烟='Dunhill' and (a.宠物='马' or c.宠物='马') -- K
or c.宠物='马' and (b.香烟='Dunhill' or d.香烟='Dunhill') or c.香烟='Dunhill' and (b.宠物='马' or d.宠物='马') -- K
or d.宠物='马' and (c.香烟='Dunhill' or e.香烟='Dunhill') or d.香烟='Dunhill' and (c.宠物='马' or e.宠物='马')) -- K
and (b.饮料='水' and (a.香烟='Blends' or c.香烟='Blends') or b.香烟='Blends' and (a.饮料='水' or c.饮料='水') -- O
or c.饮料='水' and (b.香烟='Blends' or d.香烟='Blends') or c.香烟='Blends' and (b.饮料='水' or d.饮料='水') -- O
or d.饮料='水' and (c.香烟='Blends' or e.香烟='Blends') or d.香烟='Blends' and (c.饮料='水' or e.饮料='水')) -- O
declare @t table(序号 int,房子 varchar(20),国籍 varchar(20),饮料 varchar(20),香烟 varchar(20),宠物 varchar(20))
--全部组合
insert @t select * from
(select 1 序号 union select 2 union select 3 union select 4 union select 5 ) n,
(select '红色' 房子 union select '绿色' union select '白色' union select '黄色' union select '蓝色' ) c,
(select '英国' 国籍 union select '瑞典' union select '丹麦' union select '挪威' union select '德国' ) g,
(select '茶' 饮料 union select '咖啡' union select '牛奶' union select '啤酒' union select '水' ) d,
(select 'Pall Mall' 香烟 union select 'Dunhill' union select 'Blends' union select 'Blue Master' union select 'Prince') s,
(select '狗' 宠物 union select '鸟' union select '猫' union select '马' union select '鱼' ) a
--按条件删除
delete @t where 国籍='英国' and 房子<>'红色' or 国籍<>'英国' and 房子='红色' -- A
delete @t where 国籍='瑞典' and 宠物<>'狗' or 国籍<>'瑞典' and 宠物='狗' -- B
delete @t where 国籍='丹麦' and 饮料<>'茶' or 国籍<>'丹麦' and 饮料='茶' -- C
delete @t where 房子='绿色' and 饮料<>'咖啡' or 房子<>'绿色' and 饮料='咖啡' -- E
delete @t where 香烟='Pall Mall' and 宠物<>'鸟' or 香烟<>'Pall Mall' and 宠物='鸟' -- F
delete @t where 香烟='Dunhill' and 房子<>'黄色' or 香烟<>'Dunhill' and 房子='黄色' -- G
delete @t where 序号=3 and 饮料<>'牛奶' or 序号<>3 and 饮料='牛奶' -- H
delete @t where 序号=1 and 国籍<>'挪威' or 序号<>1 and 国籍='挪威' -- I
delete @t where 香烟='Blue Master' and 饮料<>'啤酒' or 香烟<>'Blue Master' and 饮料='啤酒' -- L
delete @t where 香烟='Prince' and 国籍<>'德国' or 香烟<>'Prince' and 国籍='德国' -- M
delete @t where 序号=2 and 房子<>'蓝色' or 序号<>2 and 房子='蓝色' -- N & I
--筛选
select * from @t a,@t b,@t c,@t d,@t e
where a.序号=1 and b.序号=2 and c.序号=3 and d.序号=4 and e.序号=5
and a.国籍<>b.国籍 and a.香烟<>b.香烟 and a.饮料<>b.饮料 and a.房子<>b.房子 and a.宠物<>b.宠物
and a.国籍<>c.国籍 and a.香烟<>c.香烟 and a.饮料<>c.饮料 and a.房子<>c.房子 and a.宠物<>c.宠物
and a.国籍<>d.国籍 and a.香烟<>d.香烟 and a.饮料<>d.饮料 and a.房子<>d.房子 and a.宠物<>d.宠物
and a.国籍<>e.国籍 and a.香烟<>e.香烟 and a.饮料<>e.饮料 and a.房子<>e.房子 and a.宠物<>e.宠物
and b.国籍<>c.国籍 and b.香烟<>c.香烟 and b.饮料<>c.饮料 and b.房子<>c.房子 and b.宠物<>c.宠物
and b.国籍<>d.国籍 and b.香烟<>d.香烟 and b.饮料<>d.饮料 and b.房子<>d.房子 and b.宠物<>d.宠物
and b.国籍<>e.国籍 and b.香烟<>e.香烟 and b.饮料<>e.饮料 and b.房子<>e.房子 and b.宠物<>e.宠物
and c.国籍<>d.国籍 and c.香烟<>d.香烟 and c.饮料<>d.饮料 and c.房子<>d.房子 and c.宠物<>d.宠物
and c.国籍<>e.国籍 and c.香烟<>e.香烟 and c.饮料<>e.饮料 and c.房子<>e.房子 and c.宠物<>e.宠物
and d.国籍<>e.国籍 and d.香烟<>e.香烟 and d.饮料<>e.饮料 and d.房子<>e.房子 and d.宠物<>e.宠物
and (a.房子='绿色' and b.房子='白色' or b.房子='绿色' and c.房子='白色' -- D
or c.房子='绿色' and d.房子='白色' or d.房子='绿色' and e.房子='白色') -- D
and (b.宠物='猫' and (a.香烟='Blends' or c.香烟='Blends' ) or b.香烟='Blends' and (a.宠物='猫' or c.宠物='猫') -- J
or c.宠物='猫' and (b.香烟='Blends' or d.香烟='Blends' ) or c.香烟='Blends' and (b.宠物='猫' or d.宠物='猫') -- J
or d.宠物='猫' and (c.香烟='Blends' or e.香烟='Blends' ) or d.香烟='Blends' and (c.宠物='猫' or e.宠物='猫')) -- J
and (b.宠物='马' and (a.香烟='Dunhill' or c.香烟='Dunhill') or b.香烟='Dunhill' and (a.宠物='马' or c.宠物='马') -- K
or c.宠物='马' and (b.香烟='Dunhill' or d.香烟='Dunhill') or c.香烟='Dunhill' and (b.宠物='马' or d.宠物='马') -- K
or d.宠物='马' and (c.香烟='Dunhill' or e.香烟='Dunhill') or d.香烟='Dunhill' and (c.宠物='马' or e.宠物='马')) -- K
and (b.饮料='水' and (a.香烟='Blends' or c.香烟='Blends') or b.香烟='Blends' and (a.饮料='水' or c.饮料='水') -- O
or c.饮料='水' and (b.香烟='Blends' or d.香烟='Blends') or c.香烟='Blends' and (b.饮料='水' or d.饮料='水') -- O
or d.饮料='水' and (c.香烟='Blends' or e.香烟='Blends') or d.香烟='Blends' and (c.饮料='水' or e.饮料='水')) -- O
