一个智力小游戏(请用SQL求解)

2007-12-24 05:02:42 826 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

Python实现智力问答小游戏内附代码本篇文章将使用Python代码语言简单编写一个轻松益智的小游戏，效果如下所示： [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-QG0hhvFR-1636186979788)(https://upload-images.jianshu.io/upload_images/25002343-d7f5c51600aa5072.png?imageMogr2/auto-orient/strip%7CimageView2/2/w/1240)] 1、设计思路本项目使用SQLite建立问答题阅读详情

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分算是给参加评议的星星们的一点小意思，毕竟劳动付出精力了嘛。

这一百分算是我的新年贺礼，祝大家快乐 :)

...全文

Unreal Engine鸿蒙移植实战：环境配置、构建流程与性能优化指南游戏引擎移植是跨平台开发中的关键技术环节，其核心原理在于通过适配目标操作系统的底层接口和工具链，实现同一套代码在不同硬件和软件环境下的高效运行。这项技术的价值在于最大化代码复用率，降低多平台维护成本，并能让应用快速触达新兴生态系统的用户。在移动游戏和XR应用领域，随着鸿蒙HarmonyOS生态的快速发展，将成熟的商业引擎如Unreal Engine迁移至此平台，成为开发者探索新市场机会的重要路径。本文聚焦于Unreal Engine项目向鸿蒙系统的移植实践，详细解析了从环境搭建、源码适配到构建打包的全链路流阅读详情

tim_spac

MS-SQL Server 34,876 成员 254,635 内容

+ 关注 + 加入社区

切换为时间正序

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:这帖子打开得太慢了，也许我的电脑有点问题

tim_spac 2007-12-26

打赏
举报

星星们的酬劳:
* 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分以资鼓励

请星星们评议。

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）

一个智力小游戏(请用SQL求解)

相关推荐