上周的题可能太简单了,用典型的WITH子查询:
WITH d AS (
SELECT n,c,POWER(2,ROWNUM-1) AS id
FROM (SELECT LEVEL n FROM DUAL CONNECT BY LEVEL<=4)
,(SELECT 'R' c FROM DUAL UNION ALL SELECT 'B' c FROM DUAL UNION ALL SELECT 'G' c FROM DUAL UNION ALL SELECT 'Y' c FROM DUAL)
)
,t(cnt,n,c,bits,n0,c0,n1,c1,n2,c2,n3,c3) AS (
SELECT 1
,n
,c
,id
,n AS n0
,c AS c0
,0
,'*'
,0
,'*'
,0
,'*'
FROM d
UNION ALL
SELECT t.cnt+1
,d.n
,d.c
,t.bits+d.id
,DECODE(MOD(t.cnt,4),0,d.n,t.n0)
,DECODE(MOD(t.cnt,4),0,d.c,t.c0)
,DECODE(MOD(t.cnt,4),1,d.n,t.n1)
,DECODE(MOD(t.cnt,4),1,d.c,t.c1)
,DECODE(MOD(t.cnt,4),2,d.n,t.n2)
,DECODE(MOD(t.cnt,4),2,d.c,t.c2)
,DECODE(MOD(t.cnt,4),3,d.n,t.n3)
,DECODE(MOD(t.cnt,4),3,d.c,t.c3)
FROM t,d
WHERE cnt<16
AND BITAND(t.bits,d.id)=0
AND (MOD(t.cnt,4)=0 OR d.n=t.n OR d.c=t.c)
AND (t.cnt<4 OR d.n = DECODE(MOD(t.cnt,4),0,t.n0,1,t.n1,2,t.n2,3,t.n3) OR d.c = DECODE(MOD(t.cnt,4),0,t.c0,1,t.c1,2,t.c2,3,t.c3))
)
SELECT COUNT(*) FROM t WHERE cnt=16;
COUNT(*)
----------
8064
Elapsed: 00:00:04.42
|