34,576
社区成员
发帖
与我相关
我的任务
分享一个智力小游戏(请用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香烟的人有一个喝水的邻居
以上是爱因斯坦在20世纪初出的这个谜语。他说世界上有98％的人答不出来。
请新秀们在36小时内尽快抢答(当然,没兴趣的就算了)，
请星星们与36小时后对新秀们的答案做评价(相信您们一定能得出结果，机会留给新人吧)。
给出正确答案的前5人获得每人获10分，星星们评议出的特色奖、选择奖加获10分，
剩下的30分算是给参加评议的星星们的一点小意思，毕竟劳动付出精力了嘛。
这一百分算是我的新年贺礼，祝大家快乐 :)
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香烟的人有一个喝水的邻居
以上是爱因斯坦在20世纪初出的这个谜语。他说世界上有98％的人答不出来。
请新秀们在36小时内尽快抢答(当然,没兴趣的就算了)，
请星星们与36小时后对新秀们的答案做评价(相信您们一定能得出结果，机会留给新人吧)。
给出正确答案的前5人获得每人获10分，星星们评议出的特色奖、选择奖加获10分，
剩下的30分算是给参加评议的星星们的一点小意思，毕竟劳动付出精力了嘛。
这一百分算是我的新年贺礼，祝大家快乐 :)
...全文
请发表友善的回复…
发表回复
junon 2007-12-31
- 打赏
- 举报
高级
tim_spac 2007-12-27
- 打赏
- 举报
蛋挞：来晚了。没关系，下次再来。
winjay84 2007-12-27
- 打赏
- 举报
48 小时早过了啊
楼主不来了？
楼主不来了？
米多 2007-12-26
- 打赏
- 举报
大家都被忽悠了,,根本没人养鱼!!!
cxmcxm 2007-12-26
- 打赏
- 举报
set nocount on
declare @t1 table (no int)
insert into @t1 values(1)
insert into @t1 values(2)
insert into @t1 values(3)
insert into @t1 values(4)
insert into @t1 values(5)
declare @t2 table(color varchar(20))
insert into @t2 values('红色')
insert into @t2 values('白色')
insert into @t2 values('绿色')
insert into @t2 values('黄色')
insert into @t2 values('蓝色')
declare @t3 table(country varchar(20))
insert into @t3 values ('英国')
insert into @t3 values ('瑞典')
insert into @t3 values ('丹麦')
insert into @t3 values ('德国')
insert into @t3 values ('挪威')
declare @t4 table(drink varchar(20))
insert into @t4 values('茶')
insert into @t4 values('咖啡')
insert into @t4 values('牛奶')
insert into @t4 values('啤酒')
insert into @t4 values('水')
declare @t5 table(smoke varchar(20))
insert into @t5 values('Pall Mall')
insert into @t5 values('Dunhill')
insert into @t5 values('Blends')
insert into @t5 values('Blue Master')
insert into @t5 values('Prince')
declare @t6 table(pet varchar(20))
insert into @t6 values('狗')
insert into @t6 values('鸟')
insert into @t6 values('猫')
insert into @t6 values('马')
insert into @t6 values('鱼')
set nocount off
if object_id('tempdb..#tmp') is not null drop table #tmp
select * into #tmp from @t1 a,@t2 b,@t3 c,@t4 d,@t5 e,@t6 f
where (country='英国' and color='红色' or color<>'红色' and country<>'英国') --1、英国人住红色房子
and (country='瑞典' and pet='狗' or country<>'瑞典' and pet<>'狗') --2、瑞典人养狗
and (country='丹麦' and drink='茶' or country<>'丹麦' and drink<>'茶') --3、丹麦人喝茶
and (no=1 and color<>'白色' or no<>1) and (no=5 and color<>'绿色' or no<>5)
-- and (color<>'白色' or color='白色' and exists(select * from @t1,@t2,@t3,@t4,@t5 where no=a.no-1 and color='绿色'
-- and color<>b.color and country<>c.country and drink<>d.drink and smoke<>e.smoke and pet<>f.pet))
--4绿色房子在白色房子左面
and (color='绿色' and drink='咖啡' or color<>'绿色' and drink<>'咖啡') --5 绿色房子主人喝咖啡
and (smoke='Pall Mall' and pet='鸟' or smoke<>'Pall Mall' and pet<>'鸟') --6抽Pall Mall 香烟的人养鸟
and (color='黄色' and smoke='Dunhill' or color<>'黄色' and smoke<>'Dunhill') --7黄色房子主人抽Dunhill 香烟
and (no=3 and drink='牛奶' or no<>3 and drink<>'牛奶') --8、住在中间房子的人喝牛奶
and (country='挪威' and no=1 or country<>'挪威' and no<>1) --9挪威人住第一间房
and (smoke<>'Blends'
or smoke='blends' and exists(select * from @t1,@t2,@t3,@t4,@t5,@t6 where (no=a.no+1 or no=a.no-1) and pet='猫'
and color<>b.color and country<>c.country and drink<>d.drink and smoke<>'blend' and pet<>f.pet))
--10、抽Blends香烟的人住在养猫的人隔壁
and (pet<>'马' or pet='马' and exists(select * from @t1,@t2,@t3,@t4,@t5,@t6 where (no=a.no+1 or no=a.no-1) and smoke='Dunhill'
and color<>b.color and country<>c.country and drink<>d.drink and smoke<>e.smoke and pet<>f.pet))
--11、养马的人住抽Dunhill 香烟的人隔壁
and (smoke='Blue Master' and drink='啤酒' or smoke<>'Blue Master' and drink<>'啤酒') --12、抽Blue Master的人喝啤酒
and (country='德国' and smoke='Prince' or country<>'德国' and smoke<>'Prince') --13、德国人抽Prince香烟
and (country<>'挪威'
or country='挪威' and exists(select * from @t1,@t2,@t3,@t4,@t5,@t6 where (no=a.no+1 or no=a.no-1) and color='蓝色'
and color<>b.color and country<>c.country and drink<>d.drink and smoke<>e.smoke and pet<>f.pet))
--14、挪威人住蓝色房子隔壁
and (smoke<>'Blends' or smoke='Blends' and exists(select * from @t1,@t2,@t3,@t4,@t5,@t6 where (no=a.no+1 or no=a.no-1) and drink='水'
and color<>b.color and country<>c.country and drink<>d.drink and smoke<>e.smoke and pet<>f.pet))
--15、抽Blends香烟的人有一个喝水的邻居
order by 6,3,1
--对#tmp再筛选
--行转列
if object_id('tempdb..#tmp1') is not null drop table #tmp1
select identity(int,1,1) id,a.color color1,a.country country1,a.drink drink1,a.smoke smoke1,a.pet pet1,
b.color color2,b.country country2,b.drink drink2,b.smoke smoke2,b.pet pet2,
c.color color3,c.country country3,c.drink drink3,c.smoke smoke3,c.pet pet3,
d.color color4,d.country country4,d.drink drink4,d.smoke smoke4,d.pet pet4,
e.color color5,e.country country5,e.drink drink5,e.smoke smoke5,e.pet pet5
into #tmp1 from #tmp a,#tmp b,#tmp c,#tmp d,#tmp e
where a.no=1 and b.no=2 and c.no=3 and d.no=4 and e.no=5
and a.color<>b.color and a.country<>b.country and a.drink<>b.drink and a.smoke<>b.smoke and a.pet<>b.pet
and a.color<>c.color and a.country<>c.country and a.drink<>c.drink and a.smoke<>c.smoke and a.pet<>c.pet
and a.color<>d.color and a.country<>d.country and a.drink<>d.drink and a.smoke<>d.smoke and a.pet<>d.pet
and a.color<>e.color and a.country<>e.country and a.drink<>e.drink and a.smoke<>e.smoke and a.pet<>e.pet
and c.color<>b.color and c.country<>b.country and c.drink<>b.drink and c.smoke<>b.smoke and c.pet<>b.pet
and d.color<>b.color and d.country<>b.country and d.drink<>b.drink and d.smoke<>b.smoke and d.pet<>b.pet
and e.color<>b.color and e.country<>b.country and e.drink<>b.drink and e.smoke<>b.smoke and e.pet<>b.pet
and c.color<>d.color and c.country<>d.country and c.drink<>d.drink and c.smoke<>d.smoke and c.pet<>d.pet
and c.color<>e.color and c.country<>e.country and c.drink<>e.drink and c.smoke<>e.smoke and c.pet<>e.pet
and d.color<>e.color and d.country<>e.country and d.drink<>e.drink and d.smoke<>e.smoke and d.pet<>e.pet
--列转行
if object_id('tempdb..#tmp2') is not null drop table #tmp2
select id,1 no,color1 color,country1 country,drink1 drink,smoke1 smoke,pet1 pet
into #tmp2 from #tmp1
union all
select id,2 no,color2 color,country2 country,drink2 drink,smoke2 smoke,pet2 pet
from #tmp1
union all
select id,3 no,color3 color,country3 country,drink3 drink,smoke3 smoke,pet3 pet
from #tmp1
union all
select id,4 no,color4 color,country4 country,drink4 drink,smoke4 smoke,pet4 pet
from #tmp1
union all
select id,5 no,color5 color,country5 country,drink5 drink,smoke5 smoke,pet5 pet
from #tmp1
--进行筛选
select *
from #tmp2
where id in (select id from #tmp1 a
where exists(select count(*) from #tmp2 b where id=a.id
and (color<>'白色' or color='白色' and exists(select * from #tmp2 where id=a.id and no+1=b.no and color='绿色'))
--4绿色房子在白色房子左面
and (smoke<>'Blends'
or smoke='blends' and exists(select * from #tmp2 where id=a.id and (no=b.no+1 or no=b.no-1) and pet='猫'))
--10、抽Blends香烟的人住在养猫的人隔壁
and (pet<>'马' or pet='马' and exists(select * from #tmp2 where id=a.id and (no=b.no+1 or no=b.no-1) and smoke='Dunhill'))
--11、养马的人住抽Dunhill 香烟的人隔壁
and (country<>'挪威'
or country='挪威' and exists(select * from #tmp2 where id=a.id and (no=b.no+1 or no=b.no-1) and color='蓝色'))
--14、挪威人住蓝色房子隔壁
and (smoke<>'Blends' or smoke='Blends' and exists(select * from #tmp2 where id=a.id and (no=b.no+1 or no=b.no-1) and drink='水'))
--15、抽Blends香烟的人有一个喝水的邻居
having count(*)>=5))
order by id,no
id no color country drink smoke pet
----------- ----------- -------------------- -------------------- -------------------- -------------------- --------------------
104 1 黄色 挪威 水 Dunhill 猫
104 2 蓝色 丹麦 茶 Blends 马
104 3 红色 英国 牛奶 Pall Mall 鸟
104 4 绿色 德国 咖啡 Prince 鱼
104 5 白色 瑞典 啤酒 Blue Master 狗
第四个条件--4绿色房子在白色房子左面 如果不是指与白色房子相邻,只是在它的左面则有7种可能. 查询时间2秒
花了2个晚上,总算有结果,等着接分!
昵称被占用了 2007-12-26
- 打赏
- 举报
--结果
Num 颜色 国籍 饮料 香烟 宠物 Num 颜色 国籍 饮料 香烟 宠物 Num 颜色 国籍 饮料 香烟 宠物 Num 颜色 国籍 饮料 香烟 宠物 Num 颜色 国籍 饮料 香烟 宠物
----------- -------------------- -------------------- -------------------- -------------------- -------------------- ----------- -------------------- -------------------- -------------------- -------------------- -------------------- ----------- -------------------- -------------------- -------------------- -------------------- -------------------- ----------- -------------------- -------------------- -------------------- -------------------- -------------------- ----------- -------------------- -------------------- -------------------- -------------------- --------------------
1 白色 瑞典 啤酒 Blue Master 狗 2 绿色 德国 咖啡 Prince 鱼 3 红色 英国 牛奶 Pall Mall 鸟 4 蓝色 丹麦 茶 Blends 马 5 黄色 挪威 水 Dunhill 猫
1 黄色 挪威 水 Dunhill 猫 2 蓝色 丹麦 茶 Blends 马 3 红色 英国 牛奶 Pall Mall 鸟 4 绿色 德国 咖啡 Prince 鱼 5 白色 瑞典 啤酒 Blue Master 狗
1 黄色 挪威 水 Dunhill 猫 2 蓝色 丹麦 茶 Blends 马 3 红色 英国 牛奶 Pall Mall 鸟 4 白色 瑞典 啤酒 Blue Master 狗 5 绿色 德国 咖啡 Prince 鱼
1 绿色 德国 咖啡 Prince 鱼 2 白色 瑞典 啤酒 Blue Master 狗 3 红色 英国 牛奶 Pall Mall 鸟 4 蓝色 丹麦 茶 Blends 马 5 黄色 挪威 水 Dunhill 猫
（所影响的行数为 4 行）
（所影响的行数为 2 行）
--结论:答案是唯一的,德国人养鱼
--关键不同还是
--4、绿色房子在白色房子左面
--的理解,如果最左边不一定是第一,左面的意思也不是隔壁
--那说这个条件是否等于没说?
--这是我的理解
昵称被占用了 2007-12-26
- 打赏
- 举报
--上一个回复假定了 最左边是第一,修正下
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
--按条件删除
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 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.宠物<>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.宠物<>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.饮料='水')
)
and ( a.国籍='挪威' or e.国籍='挪威')
and (
(a.颜色='绿色' and b.颜色='白色'
or b.颜色='绿色' and c.颜色='白色'
or c.颜色='绿色' and d.颜色='白色'
or d.颜色='绿色' and e.颜色='白色')
or
(e.颜色='绿色' and d.颜色='白色'
or d.颜色='绿色' and c.颜色='白色'
or c.颜色='绿色' and b.颜色='白色'
or b.颜色='绿色' and a.颜色='白色')
)
winjay84 2007-12-26
- 打赏
- 举报
Haiwer
海阔天空
是否“左面 <> 隔壁的左面”我觉得是个语文问题
----------------
这个问题我倒是忽略了，我推理的时候是作为 “左面 = 隔壁的左面”
有必要再看看 “左面 <> 隔壁的左面”的情况
海阔天空
是否“左面 <> 隔壁的左面”我觉得是个语文问题
----------------
这个问题我倒是忽略了，我推理的时候是作为 “左面 = 隔壁的左面”
有必要再看看 “左面 <> 隔壁的左面”的情况
昵称被占用了 2007-12-26
- 打赏
- 举报
--1、坚持 左面 = 隔壁的左面
--2、修正笔误
--3、把有左面和第一字样的条件4 9合并,适合第一是左边第一和第一是右边第一两种情况求解
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
--按条件删除
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 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.宠物<>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.宠物<>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.饮料='水')
)
and ( --左边是1
a.国籍='挪威' and (a.颜色='绿色' and b.颜色='白色'
or b.颜色='绿色' and c.颜色='白色'
or c.颜色='绿色' and d.颜色='白色'
or d.颜色='绿色' and e.颜色='白色')
or
--左边是5
e.国籍='挪威' and (e.颜色='绿色' and d.颜色='白色'
or d.颜色='绿色' and c.颜色='白色'
or c.颜色='绿色' and b.颜色='白色'
or b.颜色='绿色' and a.颜色='白色')
)
--结果
Num 颜色 国籍 饮料 香烟 宠物 Num 颜色 国籍 饮料 香烟 宠物 Num 颜色 国籍 饮料 香烟 宠物 Num 颜色 国籍 饮料 香烟 宠物 Num 颜色 国籍 饮料 香烟 宠物
----------- -------------------- -------------------- -------------------- -------------------- -------------------- ----------- -------------------- -------------------- -------------------- -------------------- -------------------- ----------- -------------------- -------------------- -------------------- -------------------- -------------------- ----------- -------------------- -------------------- -------------------- -------------------- -------------------- ----------- -------------------- -------------------- -------------------- -------------------- --------------------
1 白色 瑞典 啤酒 Blue Master 狗 2 绿色 德国 咖啡 Prince 鱼 3 红色 英国 牛奶 Pall Mall 鸟 4 蓝色 丹麦 茶 Blends 马 5 黄色 挪威 水 Dunhill 猫
1 黄色 挪威 水 Dunhill 猫 2 蓝色 丹麦 茶 Blends 马 3 红色 英国 牛奶 Pall Mall 鸟 4 绿色 德国 咖啡 Prince 鱼 5 白色 瑞典 啤酒 Blue Master 狗
（所影响的行数为 2 行）
--结论:两个结果只是倒了下次序,答案是唯一的,德国人养鱼
--至于其他写法得出很多结果的,应该是条件写漏了
--帖子太慢,没有仔细看
czwand 2007-12-26
- 打赏
- 举报
德国
昵称被占用了 2007-12-26
- 打赏
- 举报
是否“左面 <> 隔壁的左面”我觉得是个语文问题
如果 左面 <> 隔壁的左面，那就没必要说了，所以我假定 左面 = 隔壁的左面
ps:这帖子打开得太慢了，也许我的电脑有点问题
如果 左面 <> 隔壁的左面，那就没必要说了，所以我假定 左面 = 隔壁的左面
ps:这帖子打开得太慢了，也许我的电脑有点问题
tim_spac 2007-12-26
- 打赏
- 举报
星星们的酬劳:
* happyflystone(无枪狙击手) 10 + 5 (维持持续的声音)
* fa_ge(鶴嘯九天) 10
* jinjazz(近身剪) 10 - 5 (犯规处罚)
* dobear_0922(do熊) 10
* fcuandy(人, 无完人;学, 无止境) 10
* Haiwer(海阔天空) 10 - 5 (犯规处罚) + 10 (详细答案)
如果没有其他提议，明日将依次结算闭帖。
* happyflystone(无枪狙击手) 10 + 5 (维持持续的声音)
* fa_ge(鶴嘯九天) 10
* jinjazz(近身剪) 10 - 5 (犯规处罚)
* dobear_0922(do熊) 10
* fcuandy(人, 无完人;学, 无止境) 10
* Haiwer(海阔天空) 10 - 5 (犯规处罚) + 10 (详细答案)
如果没有其他提议，明日将依次结算闭帖。
tim_spac 2007-12-26
- 打赏
- 举报
建议将新秀们的30分给予：
唯一给出脚本的cxmcxm(小陈)同学，10分。
liangCK(小梁)同学积极参与， springzfc, winjay84(绿绿的蛋挞), parss(往事如云) 分别以Script以外的方式给出答案
建议分别给予5分以资鼓励
请星星们评议。
唯一给出脚本的cxmcxm(小陈)同学，10分。
liangCK(小梁)同学积极参与， springzfc, winjay84(绿绿的蛋挞), parss(往事如云) 分别以Script以外的方式给出答案
建议分别给予5分以资鼓励
请星星们评议。
tim_spac 2007-12-26
- 打赏
- 举报
好像不太好给分了 :(
tim_spac 2007-12-26
- 打赏
- 举报
还有一个小时..
zrf2006 2007-12-26
- 打赏
- 举报
楼上的NB
cxmcxm 2007-12-26
- 打赏
- 举报
set nocount on
declare @t1 table (no int)
insert into @t1 values(1)
insert into @t1 values(2)
insert into @t1 values(3)
insert into @t1 values(4)
insert into @t1 values(5)
declare @t2 table(color varchar(20))
insert into @t2 values('红色')
insert into @t2 values('白色')
insert into @t2 values('绿色')
insert into @t2 values('黄色')
insert into @t2 values('蓝色')
declare @t3 table(country varchar(20))
insert into @t3 values ('英国')
insert into @t3 values ('瑞典')
insert into @t3 values ('丹麦')
insert into @t3 values ('德国')
insert into @t3 values ('挪威')
declare @t4 table(drink varchar(20))
insert into @t4 values('茶')
insert into @t4 values('咖啡')
insert into @t4 values('牛奶')
insert into @t4 values('啤酒')
insert into @t4 values('水')
declare @t5 table(smoke varchar(20))
insert into @t5 values('Pall Mall')
insert into @t5 values('Dunhill')
insert into @t5 values('Blends')
insert into @t5 values('Blue Master')
insert into @t5 values('Prince')
declare @t6 table(pet varchar(20))
insert into @t6 values('狗')
insert into @t6 values('鸟')
insert into @t6 values('猫')
insert into @t6 values('马')
insert into @t6 values('鱼')
set nocount off
select * from @t1 a,@t2 b,@t3 c,@t4 d,@t5 e,@t6 f
where (country='英国' and color='红色' or color<>'红色' and country<>'英国') --1、英国人住红色房子
and (country='瑞典' and pet='狗' or country<>'瑞典' and pet<>'狗') --2、瑞典人养狗
and (country='丹麦' and drink='茶' or country<>'丹麦' and drink<>'茶') --3、丹麦人喝茶
and (color<>'白色' or color='白色' and exists(select * from @t1,@t2,@t3,@t4,@t5 where no=a.no-1 and color='绿色'
and color<>b.color and country<>c.country and drink<>d.drink and smoke<>e.smoke and pet<>f.pet))
--4绿色房子在白色房子左面
and (color='绿色' and drink='咖啡' or color<>'绿色' and drink<>'咖啡') --5 绿色房子主人喝咖啡
and (smoke='Pall Mall' and pet='鸟' or smoke<>'Pall Mall' and pet<>'鸟') --6抽Pall Mall 香烟的人养鸟
and (color='黄色' and smoke='Dunhill' or color<>'黄色' and smoke<>'Dunhill') --7黄色房子主人抽Dunhill 香烟
and (no=3 and drink='牛奶' or no<>3 and drink<>'牛奶') --8、住在中间房子的人喝牛奶
and (country='挪威' and no=1 or country<>'挪威' and no<>1) --9挪威人住第一间房
and (smoke<>'Blends'
or smoke='blends' and exists(select * from @t1,@t2,@t3,@t4,@t5,@t6 where (no=a.no+1 or no=a.no-1) and pet='猫'
and color<>b.color and country<>c.country and drink<>d.drink and smoke<>'blend' and pet<>f.pet))
--10、抽Blends香烟的人住在养猫的人隔壁
and (pet<>'马' or pet='马' and exists(select * from @t1,@t2,@t3,@t4,@t5,@t6 where (no=a.no+1 or no=a.no-1) and smoke='Dunhill'
and color<>b.color and country<>c.country and drink<>d.drink and smoke<>e.smoke and pet<>f.pet))
--11、养马的人住抽Dunhill 香烟的人隔壁
and (smoke='Blue Master' and drink='啤酒' or smoke<>'Blue Master' and drink<>'啤酒') --12、抽Blue Master的人喝啤酒
and (country='德国' and smoke='Prince' or country<>'德国' and smoke<>'Prince') --13、德国人抽Prince香烟
and (country<>'挪威'
or country='挪威' and exists(select * from @t1,@t2,@t3,@t4,@t5,@t6 where (no=a.no+1 or no=a.no-1) and color='蓝色'
and color<>b.color and country<>c.country and drink<>d.drink and smoke<>e.smoke and pet<>f.pet))
--14、挪威人住蓝色房子隔壁
and (smoke<>'Blends' or smoke='Blends' and exists(select * from @t1,@t2,@t3,@t4,@t5,@t6 where (no=a.no+1 or no=a.no-1) and drink='水'
and color<>b.color and country<>c.country and drink<>d.drink and smoke<>e.smoke and pet<>f.pet))
--15、抽Blends香烟的人有一个喝水的邻居
order by 6,3,1得出67条记录,好象邻居的项目还可再过滤,不知如何处理
cxmcxm 2007-12-26
- 打赏
- 举报
set nocount on
declare @t1 table (no int)
insert into @t1 values(1)
insert into @t1 values(2)
insert into @t1 values(3)
insert into @t1 values(4)
insert into @t1 values(5)
declare @t2 table(color varchar(20))
insert into @t2 values('红色')
insert into @t2 values('白色')
insert into @t2 values('绿色')
insert into @t2 values('黄色')
insert into @t2 values('蓝色')
declare @t3 table(country varchar(20))
insert into @t3 values ('英国')
insert into @t3 values ('瑞典')
insert into @t3 values ('丹麦')
insert into @t3 values ('德国')
insert into @t3 values ('挪威')
declare @t4 table(drink varchar(20))
insert into @t4 values('茶')
insert into @t4 values('咖啡')
insert into @t4 values('牛奶')
insert into @t4 values('啤酒')
insert into @t4 values('水')
declare @t5 table(smoke varchar(20))
insert into @t5 values('Pall Mall')
insert into @t5 values('Dunhill')
insert into @t5 values('Blends')
insert into @t5 values('Blue Master')
insert into @t5 values('Prince')
declare @t6 table(pet varchar(20))
insert into @t6 values('狗')
insert into @t6 values('鸟')
insert into @t6 values('猫')
insert into @t6 values('马')
insert into @t6 values('鱼')
set nocount off
select * into #tmp from @t1 a,@t2 b,@t3 c,@t4 d,@t5 e,@t6 f
where (country='英国' and color='红色' or color<>'红色' and country<>'英国') --1、英国人住红色房子
and (country='瑞典' and pet='狗' or country<>'瑞典' and pet<>'狗') --2、瑞典人养狗
and (country='丹麦' and drink='茶' or country<>'丹麦' and drink<>'茶') --3、丹麦人喝茶
and (color<>'白色' or color='白色' and exists(select * from @t1,@t2,@t3,@t4,@t5 where no=a.no-1 and color='绿色'
and color<>b.color and country<>c.country and drink<>d.drink and smoke<>e.smoke and pet<>f.pet))
--4绿色房子在白色房子左面
and (color='绿色' and drink='咖啡' or color<>'绿色' and drink<>'咖啡') --5 绿色房子主人喝咖啡
and (smoke='Pall Mall' and pet='鸟' or smoke<>'Pall Mall' and pet<>'鸟') --6抽Pall Mall 香烟的人养鸟
and (color='黄色' and smoke='Dunhill' or color<>'黄色' and smoke<>'Dunhill') --7黄色房子主人抽Dunhill 香烟
and (no=3 and drink='牛奶' or no<>3 and drink<>'牛奶') --8、住在中间房子的人喝牛奶
and (country='挪威' and no=1 or country<>'挪威' and no<>1) --9挪威人住第一间房
and (smoke<>'Blends'
or smoke='blends' and exists(select * from @t1,@t2,@t3,@t4,@t5,@t6 where (no=a.no+1 or no=a.no-1) and pet='猫'
and color<>b.color and country<>c.country and drink<>d.drink and smoke<>'blend' and pet<>f.pet))
--10、抽Blends香烟的人住在养猫的人隔壁
and (pet<>'马' or pet='马' and exists(select * from @t1,@t2,@t3,@t4,@t5,@t6 where (no=a.no+1 or no=a.no-1) and smoke='Dunhill'
and color<>b.color and country<>c.country and drink<>d.drink and smoke<>e.smoke and pet<>f.pet))
--11、养马的人住抽Dunhill 香烟的人隔壁
and (smoke='Blue Master' and drink='啤酒' or smoke<>'Blue Master' and drink<>'啤酒') --12、抽Blue Master的人喝啤酒
and (country='德国' and smoke='Prince' or country<>'德国' and smoke<>'Prince') --13、德国人抽Prince香烟
and (country<>'挪威'
or country='挪威' and exists(select * from @t1,@t2,@t3,@t4,@t5,@t6 where (no=a.no+1 or no=a.no-1) and color='蓝色'
and color<>b.color and country<>c.country and drink<>d.drink and smoke<>e.smoke and pet<>f.pet))
--14、挪威人住蓝色房子隔壁
and (smoke<>'Blends' or smoke='Blends' and exists(select * from @t1,@t2,@t3,@t4,@t5,@t6 where (no=a.no+1 or no=a.no-1) and drink='水'
and color<>b.color and country<>c.country and drink<>d.drink and smoke<>e.smoke and pet<>f.pet))
--15、抽Blends香烟的人有一个喝水的邻居
order by 6,3,1
springzfc 2007-12-25
- 打赏
- 举报
快点把代码贴出来吧。。。大家好学习学习。。。
gimse7en 2007-12-25
- 打赏
- 举报
一向都很笨
加载更多回复（64）
