|
在写今天的SQL的时候,突然想起一个技巧可以用在以前的一个题目,参见本贴的#70楼,M哥发起的挑战:
倒个100升看看,桶大小分别是 43, 59, 100, 分成 50,50
把以前的SQL稍微改良一下,马上跑出来了:
with
move AS (
SELECT p ---- 源水桶编号
,m ---- 目标水桶编号
,' 43 59100' c ---- 水桶容量
FROM (SELECT LEVEL p FROM DUAL CONNECT BY LEVEL<=3),(SELECT LEVEL m FROM DUAL CONNECT BY LEVEL<=3)
WHERE p<>m
)
,t(s,path,found,rn) as (
select ' 0 0100', CAST('0 0100' AS VARCHAR2(4000)),0,1 FROM DUAL
UNION ALL
SELECT new_s
,path||','||new_s
,COUNT(CASE WHEN new_s=' 0 50 50' THEN 1 END) OVER() found
,ROW_NUMBER() OVER(PARTITION BY new_s ORDER BY LENGTH(path)) rn
FROM t
,move
,LATERAL(SELECT DECODE(1,p,ps,m,ms,SUBSTR(s,1,3))
||DECODE(2,p,ps,m,ms,SUBSTR(s,4,3))
||DECODE(3,p,ps,m,ms,SUBSTR(s,7,3)) new_s
FROM (SELECT LPAD(SUBSTR(s,(p-1)*3+1,3)-d,3) ps ,LPAD(SUBSTR(s,(m-1)*3+1,3)+d,3) ms
FROM (SELECT LEAST(TO_NUMBER(SUBSTR(s,(p-1)*3+1,3)),TO_NUMBER(SUBSTR(c,(m-1)*3+1,3)-SUBSTR(s,(m-1)*3+1,3))) d FROM DUAL)
)
)
WHERE found=0
AND TO_NUMBER(SUBSTR(t.s,(p-1)*3+1,3))<>0 -----源水桶有水
AND SUBSTR(t.s,(m-1)*3+1,3)<SUBSTR(c,(m-1)*3+1,3) -----目标水桶有空
AND INSTR(path,new_s
)=0
AND t.rn=1
)
select path from t where s=' 0 50 50'
;
PATH
------------------------------------------------------------------------------------------------------------------------------------------------------
0 0100, 43 0 57, 0 43 57, 43 43 14, 27 59 14, 27 0 73, 0 27 73, 43 27 30, 11 59 30, 11 0 89, 0 11 89, 43 11 46, 0 54 46, 43 54 3, 38 59 3, 3
8 0 62, 0 38 62, 43 38 19, 22 59 19, 22 0 78, 0 22 78, 43 22 35, 6 59 35, 6 0 94, 0 6 94, 43 6 51, 0 49 51, 43 49 8, 33 59 8, 33 0 67,
0 33 67, 43 33 24, 17 59 24, 17 0 83, 0 17 83, 43 17 40, 1 59 40, 1 0 99, 0 1 99, 43 1 56, 0 44 56, 43 44 13, 28 59 13, 28 0 72, 0 28 72, 4
3 28 29, 12 59 29, 12 0 88, 0 12 88, 43 12 45, 0 55 45, 43 55 2, 39 59 2, 39 0 61, 0 39 61, 43 39 18, 23 59 18, 23 0 77, 0 23 77, 43 23 34,
7 59 34, 7 0 93, 0 7 93, 43 7 50, 0 50 50
|
|