这是一些有趣的小问题。我们可以尝试用T-SQL来解答。
Just for fun!
大家回复时，别忘了贴出自己的代码，可以共同分享一下SQL的技巧和思维的火花。
1.
一个数学家和他多年未见的朋友在街上偶遇。朋友问：“今天对我来说是一个很特殊的日子，我的三个儿子都在今天过生日！你能算出来他们都有多大吗？”
“好，”数学家说，“但是你得跟我讲讲他们的情况。”
“好的，我给你一些提示，”那三个小孩的父亲说，“他们三个的年龄之积是36。”
“很好，”数学家说，“但我还需要更多的提示。”
“他们三个的年龄之和等于那幢房子的窗户个数。”朋友指着他们旁边的一幢房子说。
数学家考虑了一下说，“但是我还要一点信息来解你的这个难题。”
“我大儿子的眼睛是蓝色的。”朋友说。（注：如果两个儿子是双胞胎，则没有大小之分）
“哦，可以了。”数学说道。接着他给出了答案。
请问这三个小孩的年龄。
2. （这个题目貌似很有名）
教授选出两个从2到9的数，把它们的和告诉学生甲，把它们的积告诉学生乙，让他们轮流猜这两个数。
甲说：“我猜不出”
乙说：“我猜不出”
甲说：“我猜到了”
乙说：“我也猜到了”
请问这两个数是多少。
（提示：考虑这两个数是否允许相同，分两种情况）
以上两个题目，在SQL Server 2005上可用一个SQL查询给出答案。
------解决方案--------------------
改一下我的算法，也用一下CTE吧
- SQL code
DECLARE @AGE TABLE(AGE INT)INSERT INTO @AGESELECT NUMBER FROM MASTER..SPT_VALUES WHERE TYPE='P' AND NUMBER BETWEEN 0 AND 36DECLARE @HOUSE TABLE(WINDOW INT)INSERT INTO @HOUSESELECT T1.NUMBER*T2.NUMBER FROM (SELECT NUMBER FROM MASTER..SPT_VALUES WHERE TYPE='P' AND NUMBER BETWEEN 1 AND 100) T1inner JOIN (SELECT NUMBER FROM MASTER..SPT_VALUES WHERE TYPE='P' AND NUMBER BETWEEN 1 AND 100) T2ON T1.NUMBER>=T2.NUMBER;WITH MU AS (SELECT T1.AGE AS A1,T2.AGE AS A2,T3.AGE AS A3,T1.AGE+T2.AGE+T3.AGE AS HEFROM @AGE T1INNER JOIN @AGE T2 ON T1.AGE>=T2.AGE INNER JOIN @AGE T3 ON T2.AGE>=T3.AGEAND T1.AGE+T2.AGE+T3.AGE IN (SELECT WINDOW FROM @HOUSE) AND T1.AGE*T2.AGE*T3.AGE=36)SELECT A1,A2,A3 FROM MU T1 WHERE EXISTS(SELECT 1 FROM MU T2 WHERE T2.HE=T1.HE AND T2.A1<>T1.A1) AND A1>A2--9 2 2
------解决方案--------------------
怎么算出两个答案？
- SQL code
DECLARE @number TABLE(n INT)INSERT INTO @numberSELECT NUMBER FROM MASTER..SPT_VALUES WHERE TYPE='P' AND NUMBER BETWEEN 2 AND 9;with tb as(select a.n an,b.n bn,a.n+b.n [a+b],a.n*b.n [a*b] from @number a, @number b where a.n <= b.n ),tb1 as --甲猜不到，说明两个数的和不唯一(select * from tb where [a+b] in(select [a+b] from tb group by [a+b] having COUNT(1) > 1)),tb2 as --乙猜不到，说明两个数的积不唯一(select * from tb1 where [a*b] in(select [a*b] from tb1 group by [a*b] having COUNT(1) > 1)),tb3 as --甲猜到了说明两个数的和已经唯一了(select * from tb2 where [a+b] in(select [a+b] from tb2 group by [a+b] having COUNT(1) = 1)),tb4 as --乙猜到了说明两个数的和已经唯一了(select * from tb3 where [a*b] in(select [a*b] from tb3 group by [a*b] having COUNT(1) = 1))select * from tb4
------解决方案--------------------
- SQL code
--情况一：可以相等select a.number as an,b.number bn,a.number+b.number sm,a.number*b.number m into #tb from master..spt_values a,master..spt_values bwhere a.type='p' and b.type='p'and a.number between 2 and 9and b.number between 2 and 9and a.number<=b.number;with ct1 as(select * from #tb a where exists(select 1 from #tb where a.sm=sm group by sm having COUNT(1)>=2)),ct2 as(select * from ct1 a where exists(select 1 from ct1 where a.m=m group by m having COUNT(1)>=2)),ct3 as(select * from ct2 a where exists(select 1 from ct2 where a.sm=sm group by sm having COUNT(1)=1)),ct4 as(select * from ct3 a where exists(select 1 from ct3 where a.m=m group by m having COUNT(1)=1))select * from ct4drop table #tbgo--情况一：不可以相等select a.number as an,b.number bn,a.number+b.number sm,a.number*b.number m into #tb from master..spt_values a,master..spt_values bwhere a.type='p' and b.type='p'and a.number between 2 and 9and b.number between 2 and 9and a.number<b.number;with ct1 as(select * from #tb a where exists(select 1 from #tb where a.sm=sm group by sm having COUNT(1)>=2)),ct2 as(select * from ct1 a where exists(select 1 from ct1 where a.m=m group by m having COUNT(1)>=2)),ct3 as(select * from ct2 a where exists(select 1 from ct2 where a.sm=sm group by sm having COUNT(1)=1)),ct4 as(select * from ct3 a where exists(select 1 from ct3 where a.m=m group by m having COUNT(1)=1))select * from ct4drop table #tb(36 行受影响)an bn sm m----------- ----------- ----------- -----------3 4 7 123 6 9 18(2 行受影响)(28 行受影响)an bn sm m----------- ----------- ----------- -----------3 6 9 184 6 10 24(2 行受影响)