SQL 谜题(父亲的邮票)

问题：父亲需要些1分,2分,3分,5分,10分的邮票,
其中两种各买四张,另外的三种各买三张 
我忘记是哪几种了?他给了我一些10分硬币,金额刚好买这些邮票

计算及分析过程：

--通过极限算法,若都是3,则最小值是63,若都是4,则最大值是84，且能被10整除，则是70和80

1)BETWEEN (1+2+3+5+10)*3 AND (1+2+3+5+10)*4

2)X/10=整数

CREATE TABLE #(VAL INT)

INSERT INTO #
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 5 UNION ALL
SELECT 10 

CREATE TABLE #2(VAL INT)
INSERT INTO #2
SELECT 4 UNION ALL
SELECT 3  

WITH CTE AS
(
SELECT #.VAL VAL,#2.VAL VAL1 FROM # JOIN #2 ON 1=1)

SELECT 
RTRIM(C1.VAL1)+'张'+RTRIM(C1.VAL)+'分,'+
RTRIM(C2.VAL1)+'张'+RTRIM(C2.VAL)+'分,'+
RTRIM(C3.VAL1)+'张'+RTRIM(C3.VAL)+'分,'+
RTRIM(C4.VAL1)+'张'+RTRIM(C4.VAL)+'分,'+
RTRIM(C5.VAL1)+'张'+RTRIM(C5.VAL)+'分' Result
FROM CTE C1,CTE C2,CTE C3,CTE C4,CTE C5 
WHERE C1.VAL1+C2.VAL1++C3.VAL1++C4.VAL1++C5.VAL1=17
AND C1.VAL=1 AND C2.VAL=2 AND C3.VAL=3 AND C4.VAL=5 AND C5.VAL=10
AND C1.VAL1*C1.VAL+C2.VAL1*C2.VAL+C3.VAL1*C3.VAL+C4.VAL1*C4.VAL+C5.VAL1*C5.VAL IN(70,80)

执行结果：

转载于:https://www.cnblogs.com/martintuan/p/4917011.html