Play with SQL (Mission 1)
这是一些有趣的小问题。我们可以尝试用T-SQL来解答。
Just for fun!
大家回复时，别忘了贴出自己的代码，可以共同分享一下SQL的技巧和思维的火花。
1.
一个数学家和他多年未见的朋友在街上偶遇。朋友问：“今天对我来说是一个很特殊的日子，我的三个儿子都在今天过生日！你能算出来他们都有多大吗？”
“好，”数学家说，“但是你得跟我讲讲他们的情况。”
“好的，我给你一些提示，”那三个小孩的父亲说，“他们三个的年龄之积是36。”
“很好，”数学家说，“但我还需要更多的提示。”
“他们三个的年龄之和等于那幢房子的窗户个数。”朋友指着他们旁边的一幢房子说。
数学家考虑了一下说，“但是我还要一点信息来解你的这个难题。”
“我大儿子的眼睛是蓝色的。”朋友说。（注：如果两个儿子是双胞胎，则没有大小之分）
“哦，可以了。”数学说道。接着他给出了答案。
请问这三个小孩的年龄。
2. （这个题目貌似很有名）
教授选出两个从2到9的数，把它们的和告诉学生甲，把它们的积告诉学生乙，让他们轮流猜这两个数。
甲说：“我猜不出”
乙说：“我猜不出”
甲说：“我猜到了”
乙说：“我也猜到了”
请问这两个数是多少。
（提示：考虑这两个数是否允许相同，分两种情况）
以上两个题目，在SQL Server 2005上可用一个SQL查询给出答案。
[解决办法]
改一下我的算法，也用一下CTE吧
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
[解决办法]
怎么算出两个答案？
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
[解决办法]
--情况一：可以相等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 行受影响)
[解决办法]
1、select top 36 identity(int,1,1) x into #t from syscolumnsgoselect t1.x,t2.x,t3.xfrom #t t1,#t t2,#t t3where t1.x*t2.x*t3.x=36 --和 and t1.x>=t2.x and t1.x>=t2.x and t2.x>=t3.x --按照年龄排序 and (t1.x+t2.x+t3.x) in --只有一重情况在知道和的时候无法确定，就是和有多个 (select (t1.x+t2.x+t3.x) from #t t1,#t t2,#t t3 where t1.x*t2.x*t3.x=36 and t1.x>=t2.x and t1.x>=t2.x and t2.x>=t3.x group by (t1.x+t2.x+t3.x) having count(*)>1) and t1.x>t2.x --有老大go2、我怎么算出两个答案？4,4 6,3
[解决办法]
思考语句也贴出来吧，懒得想了（后面两列是为了辅助思考）
select top 8 identity(int,2,1) x into #t2 from syscolumnsgoselect t1.x,t2.x ,(t1.x +t2.x),(t1.x *t2.x)from #t2 t1, #t2 t2where t1.x>=t2.x --老规矩，先排序 and (t1.x+t2.x )not in (4,5,17,18) --把知道和就能知道组合的去掉 and (t1.x * t2.x ) not in (select t1.x * t2.x from #t2 t1, #t2 t2 --把剩下范围中知道积就能唯一确定的去掉 where t1.x>=t2.x and (t1.x+t2.x )not in (4,5,17,18) group by ( t1.x * t2.x) having count(*)=1) and (t1.x +t2.x) in --把知道何就知道两个值的取出来 ( select t1.x+t2.x from #t2 t1, #t2 t2 where t1.x>=t2.x --老规矩，先排序 and (t1.x+t2.x )not in (4,5,17,18) --把知道和就能知道组合的去掉 and (t1.x * t2.x ) not in (select t1.x * t2.x from #t2 t1, #t2 t2 --把剩下范围知道积就能唯一确定的去掉 where t1.x>=t2.x --老规矩，先排序 and (t1.x+t2.x )not in (4,5,17,18) --把知道和就能知道组合的去掉 group by ( t1.x * t2.x) having count(*)=1) group by ( t1.x + t2.x) having count(*)=1 ) and (t1.x *t2.x) not in --在这个结果范围里知道积 有2种以上可能性的干掉 ( select t1.x*t2.x from #t2 t1, #t2 t2 where t1.x>=t2.x --老规矩，先排序 and (t1.x+t2.x )not in (4,5,17,18) --把知道和就能知道组合的去掉 and (t1.x * t2.x ) not in (select t1.x * t2.x from #t2 t1, #t2 t2 --把剩下范围中知道积就能唯一确定的去掉 where t1.x>=t2.x and (t1.x+t2.x )not in (4,5,17,18) group by ( t1.x * t2.x) having count(*)=1) and (t1.x +t2.x) in --把知道何就知道两个值的取出来 ( select t1.x+t2.x from #t2 t1, #t2 t2 where t1.x>=t2.x --老规矩，先排序 and (t1.x+t2.x )not in (4,5,17,18) --把知道和就能知道组合的去掉 and (t1.x * t2.x ) not in (select t1.x * t2.x from #t2 t1, #t2 t2 --把剩下范围知道积就能唯一确定的去掉 where t1.x>=t2.x --老规矩，先排序 and (t1.x+t2.x )not in (4,5,17,18) --把知道和就能知道组合的去掉 group by ( t1.x * t2.x) having count(*)=1) group by ( t1.x + t2.x) having count(*)=1) group by ( t1.x * t2.x) having count(*)>1 )
