查看完整版本: 如何用SQL实现？[超牛（转）]

如何用SQL实现？[超牛（转）]

1、在一条街上，有5座房子，喷了5种颜色。
2、每个房里住着不同国籍的人
3、每个人喝不同的饮料，抽不同品牌的香烟，养不同的宠物
问题是：谁养鱼？
提示：
　　1、英国人住红色房子
　　2、瑞典人养狗
　　3、丹麦人喝茶
　　4、绿色房子在白色房子左面
　　5、绿色房子主人喝咖啡
　　6、抽Pall   Mall   香烟的人养鸟
　　7、黄色房子主人抽Dunhill   香烟
　　8、住在中间房子的人喝牛奶
　　9、   挪威人住第一间房
　　10、抽Blends香烟的人住在养猫的人隔壁
　　11、养马的人住抽Dunhill   香烟的人隔壁
　　12、抽Blue   Master的人喝啤酒
　　13、德国人抽Prince香烟
　　14、挪威人住蓝色房子隔壁
　　15、抽Blends香烟的人有一个喝水的邻居

declare @t table (
Num int,
颜色 varchar(20),
国籍 varchar(20),
饮料 varchar(20),
香烟 varchar(20),
宠物 varchar(20)
)

---全部组合
insert @t
select *
from (
select 1 as num
union all select 2
union all select 3
union all select 4
union all select 5
) as n,(
select '红色' as 颜色
union all select '绿色'
union all select '白色'
union all select '黄色'
union all select '蓝色'
) as c,(
select '英国' as 国籍
union all select '瑞典'
union all select '丹麦'
union all select '挪威'
union all select '德国'
) as g,(
select '茶' as 饮料
union all select '咖啡'
union all select '牛奶'
union all select '啤酒'
union all select '水'
) as d,(
select 'Pall Mall' as 香烟
union all select 'Dunhill'
union all select 'Blends'
union all select 'Blue Master'
union all select 'Prince'
) as s,(
select '狗' as 宠物
union all select '鸟'
union all select '猫'
union all select '马'
union all select '鱼'
) as a
select * from @t

--按条件删除
delete @t
where 国籍='英国'
and 颜色<>'红色'

delete @t
where 国籍<>'英国'
and 颜色='红色'

delete @t
where 国籍='瑞典'
and 宠物<>'狗'

delete @t
where 国籍<>'瑞典'
and 宠物='狗'

delete @t
where 国籍='丹麦'
and 饮料<>'茶'

delete @t
where 国籍<>'丹麦'
and 饮料='茶'

delete @t
where 颜色='绿色'
and 饮料<>'咖啡'

delete @t
where 颜色<>'绿色'
and 饮料='咖啡'

delete @t
where 香烟='Pall Mall'
and 宠物<>'鸟'

delete @t
where 香烟<>'Pall Mall'
and 宠物='鸟'

delete @t
where 香烟='Dunhill'
and 颜色<>'黄色'

delete @t
where 香烟<>'Dunhill'
and 颜色='黄色'

delete @t
where num=3
and 饮料<>'牛奶'

delete @t
where num<>3
and 饮料='牛奶'

delete @t
where num=1
and 国籍<>'挪威'

delete @t
where num<>1
and 国籍='挪威'

delete @t
where 香烟='Blue Master'
and 饮料<>'啤酒'

delete @t
where 香烟<>'Blue Master'
and 饮料='啤酒'

delete @t
where 香烟='Prince'
and 国籍<>'德国'

delete @t
where 香烟<>'Prince'
and 国籍='德国'
select * from @t order by num asc
--筛选
select * from @t a,@t b,@t c,@t d,@t e
where a.num=1
and b.num=2
and c.num=3
and d.num=4
and e.num=5
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.宠物<>c.宠物

and a.宠物<>d.宠物
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 a.宠物<>e.宠物

and b.宠物<>c.宠物
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.宠物<>d.宠物

and b.宠物<>e.宠物
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.宠物<>d.宠物

and c.宠物<>e.宠物
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 d.宠物<>e.宠物

and (a.颜色='绿色' and b.颜色='白色'
or b.颜色='绿色' and c.颜色='白色'
or c.颜色='绿色' and d.颜色='白色'
or d.颜色='绿色' and e.颜色='白色')

and (b.宠物='猫' and (a.香烟='Blends' or c.香烟='Blends')
or b.香烟='Blends' and (a.宠物='猫' or c.宠物='猫')
or c.宠物='猫' and (b.香烟='Blends' or d.香烟='Blends')
or c.香烟='Blends' and (b.宠物='猫' or d.宠物='猫')
or d.宠物='猫' and (c.香烟='Blends' or e.香烟='Blends')
or d.香烟='Blends' and (c.宠物='猫' or e.宠物='猫')
)

and (b.宠物='马' and (a.香烟='Dunhill' or c.香烟='Dunhill')
or b.香烟='Dunhill' and (a.宠物='马' or c.宠物='马')
or c.宠物='马' and (b.香烟='Dunhill' or d.香烟='Dunhill')
or c.香烟='Dunhill' and (b.宠物='马' or d.宠物='马')
or d.宠物='马' and (c.香烟='Dunhill' or e.香烟='Dunhill')
or d.香烟='Dunhill' and (c.宠物='马' or e.宠物='马')
)

and (b.国籍='挪威' and (a.颜色='蓝色' or c.颜色='蓝色')
or b.颜色='蓝色' and (a.国籍='挪威' or c.国籍='挪威')
or c.国籍='挪威' and (b.颜色='蓝色' or d.颜色='蓝色')
or c.颜色='蓝色' and (b.国籍='挪威' or d.国籍='挪威')
or d.国籍='挪威' and (c.颜色='蓝色' or e.颜色='蓝色')
or d.颜色='蓝色' and (c.国籍='挪威' or e.国籍='挪威')
)

and (b.饮料='水' and (a.香烟='Blends' or c.香烟='Blends')
or b.香烟='Blends' and (a.饮料='水' or c.饮料='水')
or c.饮料='水' and (b.香烟='Blends' or d.香烟='Blends')
or c.香烟='Blends' and (b.饮料='水' or d.饮料='水')
or d.饮料='水' and (c.香烟='Blends' or e.香烟='Blends')
or d.香烟='Blends' and (c.饮料='水' or e.饮料='水')
)

有点晕！！！voora30soya1