# ITPUB SQL大赛第二期（二）

SQL1:

M      N         AllCnt

2      5          92

SQL2:

M      N         NoReptCnt

2      5          xx

M      N         AllCnt           NoReptCnt

2      5          92                xx

Oracle变量定义如下（以M=2, N=5为例）：

var m number;

exec :m:=2;

var n number;

exec :n:=5;

SQL> VAR N NUMBER
SQL> VAR M NUMBER
SQL> EXEC :N := 5

PL/SQL 过程已成功完成。

SQL> EXEC :M := 2

PL/SQL 过程已成功完成。

SQL> SET PAGES 100 LINES 120 TIMING ON

SQL> with i as
2  (select rownum i from dual connect by rownum <= :n),
3  j as
4  (select rownum j from dual connect by rownum <= :n),
5  position as
6  (select i, j
7  from i, j),
8  b as
9  (select rownum - 1 b from dual connect by rownum <= 2),
10  b_line as
11  (select replace(sys_connect_by_path(b, ','), ',', '') line
12  from b
13  where level = :n
14  connect by level <= :n),
15  lines as
16  (select rownum, line from b_line
17  where instr(line, 1, 1, :m) > 0
18  and instr(line, 0, 1, :n - :m) > 0),
19  lines_result as
20  (select replace(sys_connect_by_path(line, ','), ',', '') result
21  from lines
22  where level = :n
23  connect by level <= :n)
24  select :m M, :n N, count(*) AllCnt, count(distinct greatest(res1, res2, res3, res4, res5, res6, res7, res8)) NoReptCnt
25  from (
26  select result res1, reverse(result) res2,
27  (select replace(wmsys.wm_concat(substr(result, (i-1)*:n + 1, :n)), ',', '') from (select i from i order by i desc)) res3,
28  (select replace(wmsys.wm_concat(reverse(substr(result, (i-1)*:n + 1, :n))), ',', '') from (select i from i order by i)) res4,
29  (select replace(wmsys.wm_concat(substr(result, (i-1)*:n + j, 1)), ',' ,'') from (select i, j from position order by j desc, i)) res5,
30  (select replace(wmsys.wm_concat(substr(result, (i-1)*:n + j, 1)), ',' ,'') from (select i, j from position order by j, i)) res6,
31  (select replace(wmsys.wm_concat(substr(result, (i-1)*:n + j, 1)), ',' ,'') from (select i, j from position order by j, i desc)) res7,
32  (select replace(wmsys.wm_concat(substr(result, (i-1)*:n + j, 1)), ',' ,'') from (select i, j from position order by j desc, i desc)) res8
33  from (
34  select result, j col, (j-i) l, (j+i) r, sum(substr(result, (i-1)*:n + j, 1)) c
35  from lines_result, position
36  group by grouping sets ((result, j), (result, (j-i)), (result, (j+i))))
37  group by result
38  having max(case when l is null and r is null then c end) = :m
39  and max(case when col is null and l is null then c end) <= :m
40  and max(case when col is null and r is null then c end) <= :m
41  );

M          N     ALLCNT  NOREPTCNT
---------- ---------- ---------- ----------
2          5         92         14

SQL> with i as
2  (select rownum i from dual connect by rownum <= :n),
3  j as
4  (select rownum j from dual connect by rownum <= :n),
5  position as
6  (select i, j from i, j),
7  trim as
8  (select sys_connect_by_path(level -1, ' ') str
9  from dual
10  where level = :m + 1
11  connect by level <= :m + 1),
12  b as
13  (select rownum - 1 b from dual connect by rownum <= 2),
14  b_line as
15  (select sys_connect_by_path(b, ',') line
16  from b
17  where level = :n
18  connect by level <= :n),
19  lines as
20  (select rownum, replace(line, ',', '') line from b_line
21  where instr(line, 1, 1, :m) > 0
22  and instr(line, 0, 1, :n - :m) > 0),
23  lines_result as
24  (select replace(sys_connect_by_path(line, ','), ',', '') result
25  from lines
26  where level = :n
27  connect by level <= :n)
28  select :m M, :n N, count(*) AllCnt, count(distinct greatest(res1, reverse(res1), res2, reverse(res2), res3, reverse(res3), res4, reverse(res4))) NoReptCnt
29  from (
30  select result res1,
31  (select replace(sys_connect_by_path(reverse(substr(result, (level-1)*:n + 1, :n)), ','), ',', '') from dual where level = :n connect by level <= :n) res2,
32  (select replace(wmsys.wm_concat(substr(result, (i-1)*:n + j, 1)), ',' ,'') from (select i, j from position order by j desc, i)) res3,
33  (select replace(wmsys.wm_concat(substr(result, (i-1)*:n + j, 1)), ',' ,'') from (select i, j from position order by j, i)) res4
34  from lines_result, i
35  group by result
36  having sum(substr(result, (i-1)*:n + 1, :n)) = to_number(lpad(:m, :n, :m))
37  and ltrim(sum(substr(result, (i-1)*:n + 1, :n)*power(10, i-1)), (select str from trim)) is null
38  and ltrim(sum(substr(result, (i-1)*:n + 1, :n)*power(10, :n-i)), (select str from trim)) is null
39  );

M          N     ALLCNT  NOREPTCNT
---------- ---------- ---------- ----------
2          5         92         14

SQL> exec :n := 6

PL/SQL 过程已成功完成。

SQL> /

M          N     ALLCNT  NOREPTCNT
---------- ---------- ---------- ----------
2          6       1097        155

SQL> with trim as
2  (select sys_connect_by_path(level - 1, ' ') str
3  from dual
4  where level = :m + 1
5  connect by level <= :m + 1),
6  line_count as
7  (select (select round(power(2, sum(log(2, rownum)))) from dual connect by rownum <= :n)
8     / (select round(power(2, sum(log(2, rownum)))) from dual connect by rownum <= :m)
9     / (select round(power(2, sum(log(2, rownum)))) from dual connect by rownum <= :n - :m)
10  from dual),
11  b as
12  (select rownum - 1 b from dual connect by rownum <= 2),
13  b_line (line, cnt) as
14  (select b || '', b from b
15  union all
16  select b || line, b + cnt
17  from b, b_line
18  where cnt <= :m)
19  cycle line set dup_line to 'y' default 'n',
20  lines as
21  (select line
22  from b_line
23  where length(line) = :n
24  and cnt = :m
25  and rownum <= (select * from line_count)),
26  line_result (result, cnt, r, l) as
27  (select line || '', to_number(line), to_number(line), to_number(line)
28  from lines
29  union all
30  select line || result, to_number(line) + cnt, to_number(line) + 10*r, to_number(line) + l/10
31  from lines, line_result
32  where ltrim(cnt, (select str from trim)) is null
33  and ltrim(r, (select str from trim)) is null
34  and ltrim(l, (select str || '.' from trim)) is null
35  and instr(case when length(result) >= :n*(:n-:m+1) then to_char(cnt, rpad('0', :n, '9')) else '9' end, '0', 1) = 0
36  and instr(case when :n-:m+2 < :n and length(result) >= :n*(:n-:m+2) then to_char(cnt, rpad('0', :n, '9')) else '9' end, '1', 1) = 0)
37  cycle result set dup_result to 'y' default 'n',
38  result as
39  (select result, cnt, r, l from line_result
40  where rownum <= power((select * from line_count), :n))
41  select :m M, :n N, count(*) AllCnt, count(distinct greatest(res1, reverse(res1), res2, reverse(res2), res3, reverse(res3), res4, reverse(res4))) NoReptCnt
42  from (
43  select result res1,
44  (select replace(sys_connect_by_path(reverse(substr(result, (level-1)*:n + 1, :n)), ','), ',', '') from dual where level = :n connect by level <= :n) res2,
45  (select replace(sys_connect_by_path(substr(result, mod(level-1,:n)*:n + ceil(level/:n), 1), ','), ',', '') from dual where level = :n*:n connect by level <= :n*:n) res3,
46  (select replace(sys_connect_by_path(substr(result, mod(level-1,:n)*:n + :n-ceil(level/:n)+1, 1), ','), ',', '') from dual where level = :n*:n connect by level <= :n*:n) res4
47  from result
48  where length(result) = :n*:n
49  and cnt = to_number(lpad(:m, :n, :m))
50  and ltrim(r, (select str from trim)) is null
51  and ltrim(l, (select str || '.' from trim)) is null
52  );

M          N     ALLCNT  NOREPTCNT
---------- ---------- ---------- ----------
2          6       1097        155

SQL> exec :m := 3

PL/SQL 过程已成功完成。

SQL> /

M          N     ALLCNT  NOREPTCNT
---------- ---------- ---------- ----------
3          6      14412       1811

SQL> with trim as
2  (select sys_connect_by_path(level - 1, ' ') str
3  from dual
4  where level = :m + 1
5  connect by level <= :m + 1),
6  b as
7  (select rownum - 1 b from dual connect by rownum <= 2),
8  b_line (n, line, cnt) as
9  (select 1 n, to_char(b), b from b
10  union all
11  select n + 1, line || b, cnt + b
12  from b, b_line
13  where n < :n
14  and cnt <= :m),
15  lines as
16  (select line
17  from b_line
18  where cnt = :m
19  and length(line) = :n),
20  line_result (n, result, cnt, r, l) as
21  (select 1 n, line, to_number(line), to_number(line), to_number(line)
22  from lines
23  union all
24  select n + 1, result || line, to_number(line) + cnt, to_number(line) + 10*r, to_number(line) + l/10
25  from lines, line_result
26  where n < :n
27  and ltrim(to_number(line) + cnt, (select str from trim)) is null
28  and ltrim(to_number(line) + 10*r, (select str from trim)) is null
29  and ltrim(to_number(line) + l/10, (select str || '.' from trim)) is null
30  and instr(case when length(result) >= :n*(:n-:m+1) then to_char(cnt, rpad('0', :n, '9')) else '9' end, '0', 1) = 0
31  and instr(case when :n-:m+2 < :n and length(result) >= :n*(:n-:m+2) then to_char(cnt, rpad('0', :n, '9')) else '9' end, '1', 1) = 0)
32  select :m M, :n N, count(*) AllCnt, count(distinct greatest(res1, reverse(res1), res2, reverse(res2), res3, reverse(res3), res4, reverse(res4))) NoReptCnt
33  from (
34  select result res1,
35  (select replace(sys_connect_by_path(reverse(substr(result, (level-1)*:n + 1, :n)), ','), ',', '') from dual where level = :n connect by level <= :n) res2,
36  (select replace(sys_connect_by_path(substr(result, mod(level-1,:n)*:n + ceil(level/:n), 1), ','), ',', '') from dual where level = :n*:n connect by level <= :n*:n) res3,
37  (select replace(sys_connect_by_path(substr(result, mod(level-1,:n)*:n + :n-ceil(level/:n)+1, 1), ','), ',', '') from dual where level = :n*:n connect by level <= :n*:n) res4
38  from line_result
39  where length(result) = :n*:n
40  and cnt = to_number(lpad(:m, :n, :m))
41  and ltrim(r, (select str from trim)) is null
42  and ltrim(l, (select str || '.' from trim)) is null
43  );

M          N     ALLCNT  NOREPTCNT
---------- ---------- ---------- ----------
2          6       1097        155

SQL> exec :m := 3

PL/SQL 过程已成功完成。

SQL> /

M          N     ALLCNT  NOREPTCNT
---------- ---------- ---------- ----------
3          6      14412       1811

• 博文量
1954
• 访问量
10719794