# ITPUB SQL大赛第二期（一）

SQL>  with c as
2  (select rownum - 1 c from dual connect by rownum <= 2),
3  lines as
4  (select to_number(c1.c || c2.c || c3.c || c4.c || c5.c || c6.c) line, c1.c c1, c2.c c2, c3.c c3, c4.c c4, c5.c c5, c6.c c6
5  from c c1, c c2, c c3, c c4, c c5, c c6
6  where c1.c + c2.c + c3.c + c4.c + c5.c + c6.c = 2
7  order by 1 desc),
8  result as (select rownum,
9     ltrim(to_char(line1.line, '099999'))
10             || chr(10) || ltrim(to_char(line2.line, '099999'))
11             || chr(10) || ltrim(to_char(line3.line, '099999'))
12             || chr(10) || ltrim(to_char(line4.line, '099999'))
13             || chr(10) || ltrim(to_char(line5.line, '099999'))
14             || chr(10) || ltrim(to_char(line6.line, '099999')) result1,
15     reverse(ltrim(to_char(line1.line, '099999')))
16             || chr(10) || reverse(ltrim(to_char(line2.line, '099999')))
17             || chr(10) || reverse(ltrim(to_char(line3.line, '099999')))
18             || chr(10) || reverse(ltrim(to_char(line4.line, '099999')))
19             || chr(10) || reverse(ltrim(to_char(line5.line, '099999')))
20             || chr(10) || reverse(ltrim(to_char(line6.line, '099999'))) result2,
21     ltrim(to_char(line6.line, '099999'))
22             || chr(10) || ltrim(to_char(line5.line, '099999'))
23             || chr(10) || ltrim(to_char(line4.line, '099999'))
24             || chr(10) || ltrim(to_char(line3.line, '099999'))
25             || chr(10) || ltrim(to_char(line2.line, '099999'))
26             || chr(10) || ltrim(to_char(line1.line, '099999')) result3,
27     reverse(ltrim(to_char(line6.line, '099999')))
28             || chr(10) || reverse(ltrim(to_char(line5.line, '099999')))
29             || chr(10) || reverse(ltrim(to_char(line4.line, '099999')))
30             || chr(10) || reverse(ltrim(to_char(line3.line, '099999')))
31             || chr(10) || reverse(ltrim(to_char(line2.line, '099999')))
32             || chr(10) || reverse(ltrim(to_char(line1.line, '099999'))) result4,
33     line1.c1 || line2.c1 || line3.c1 || line4.c1 || line5.c1  || line6.c1
34             || chr(10) || line1.c2 || line2.c2 || line3.c2 || line4.c2 || line5.c2 || line6.c2
35             || chr(10) || line1.c3 || line2.c3 || line3.c3 || line4.c3 || line5.c3 || line6.c3
36             || chr(10) || line1.c4 || line2.c4 || line3.c4 || line4.c4 || line5.c4 || line6.c4
37             || chr(10) || line1.c5 || line2.c5 || line3.c5 || line4.c5 || line5.c5 || line6.c5
38             || chr(10) || line1.c6 || line2.c6 || line3.c6 || line4.c6 || line5.c6 || line6.c6 result5,
39     line6.c1 || line5.c1 || line4.c1 || line3.c1 || line2.c1 || line1.c1
40             || chr(10) || line6.c2 || line5.c2 || line4.c2 || line3.c2 || line2.c2 || line1.c2
41             || chr(10) || line6.c3 || line5.c3 || line4.c3 || line3.c3 || line2.c3 || line1.c3
42             || chr(10) || line6.c4 || line5.c4 || line4.c4 || line3.c4 || line2.c4 || line1.c4
43             || chr(10) || line6.c5 || line5.c5 || line4.c5 || line3.c5 || line2.c5 || line1.c5
44             || chr(10) || line6.c6 || line5.c6 || line4.c6 || line3.c6 || line2.c6 || line1.c6 result6,
45     line1.c6 || line2.c6 || line3.c6 || line4.c6 || line5.c6 || line6.c6
46             || chr(10) || line1.c5 || line2.c5 || line3.c5 || line4.c5 || line5.c5 || line6.c5
47             || chr(10) || line1.c4 || line2.c4 || line3.c4 || line4.c4 || line5.c4 || line6.c4
48             || chr(10) || line1.c3 || line2.c3 || line3.c3 || line4.c3 || line5.c3 || line6.c3
49             || chr(10) || line1.c2 || line2.c2 || line3.c2 || line4.c2 || line5.c2 || line6.c2
50             || chr(10) || line1.c1 || line2.c1 || line3.c1 || line4.c1 || line5.c1 || line6.c1 result7,
51     line6.c6 || line5.c6 || line4.c6 || line3.c6 || line2.c6 || line1.c6
52             || chr(10) || line6.c5 || line5.c5 || line4.c5 || line3.c5 || line2.c5 || line1.c5
53             || chr(10) || line6.c4 || line5.c4 || line4.c4 || line3.c4 || line2.c4 || line1.c4
54             || chr(10) || line6.c3 || line5.c3 || line4.c3 || line3.c3 || line2.c3 || line1.c3
55             || chr(10) || line6.c2 || line5.c2 || line4.c2 || line3.c2 || line2.c2 || line1.c2
56             || chr(10) || line6.c1 || line5.c1 || line4.c1 || line3.c1 || line2.c1 || line1.c1 result8
57  from lines line1, lines line2, lines line3, lines line4, lines line5, lines line6
58  where line1.line + line2.line + line3.line + line4.line + line5.line + line6.line = 222222
59  and ltrim(to_char(line1.line + 10*line2.line + 100*line3.line + 1000*line4.line + 10000*line5.line + 100000*line6.line), '012') is null
60  and ltrim(to_char(100000*line1.line + 10000*line2.line + 1000*line3.line + 100*line4.line + 10*line5.line + line6.line), '012') is null
61  )
62  select count(distinct greatest(result1, result2, result3, result4, result5, result6, result7, result8)) cn
63  from result;

CN
----------
155

SQL>  with c as
2  (select rownum - 1 c from dual connect by rownum <= 2),
3  lines as
4  (select to_number(c1.c || c2.c || c3.c || c4.c || c5.c || c6.c) line, c1.c c1, c2.c c2, c3.c c3, c4.c c4, c5.c c5, c6.c c6
5  from c c1, c c2, c c3, c c4, c c5, c c6
6  where c1.c + c2.c + c3.c + c4.c + c5.c + c6.c = 2
7  ),
8  result as (select /*+ ordered */
9     ltrim(to_char(line1.line, '099999'))
10             || ltrim(to_char(line2.line, '099999'))
11             || ltrim(to_char(line3.line, '099999'))
12             || ltrim(to_char(line4.line, '099999'))
13             || ltrim(to_char(line5.line, '099999'))
14             || ltrim(to_char(line6.line, '099999')) result1,
15     reverse(ltrim(to_char(line1.line, '099999')))
16             || reverse(ltrim(to_char(line2.line, '099999')))
17             || reverse(ltrim(to_char(line3.line, '099999')))
18             || reverse(ltrim(to_char(line4.line, '099999')))
19             || reverse(ltrim(to_char(line5.line, '099999')))
20             || reverse(ltrim(to_char(line6.line, '099999'))) result2,
21     ltrim(to_char(line6.line, '099999'))
22             || ltrim(to_char(line5.line, '099999'))
23             || ltrim(to_char(line4.line, '099999'))
24             || ltrim(to_char(line3.line, '099999'))
25             || ltrim(to_char(line2.line, '099999'))
26             || ltrim(to_char(line1.line, '099999')) result3,
27     reverse(ltrim(to_char(line6.line, '099999')))
28             || reverse(ltrim(to_char(line5.line, '099999')))
29             || reverse(ltrim(to_char(line4.line, '099999')))
30             || reverse(ltrim(to_char(line3.line, '099999')))
31             || reverse(ltrim(to_char(line2.line, '099999')))
32             || reverse(ltrim(to_char(line1.line, '099999'))) result4,
33     line1.c1 || line2.c1 || line3.c1 || line4.c1 || line5.c1  || line6.c1
34             || line1.c2 || line2.c2 || line3.c2 || line4.c2 || line5.c2 || line6.c2
35             || line1.c3 || line2.c3 || line3.c3 || line4.c3 || line5.c3 || line6.c3
36             || line1.c4 || line2.c4 || line3.c4 || line4.c4 || line5.c4 || line6.c4
37             || line1.c5 || line2.c5 || line3.c5 || line4.c5 || line5.c5 || line6.c5
38             || line1.c6 || line2.c6 || line3.c6 || line4.c6 || line5.c6 || line6.c6 result5,
39     line6.c1 || line5.c1 || line4.c1 || line3.c1 || line2.c1 || line1.c1
40             || line6.c2 || line5.c2 || line4.c2 || line3.c2 || line2.c2 || line1.c2
41             || line6.c3 || line5.c3 || line4.c3 || line3.c3 || line2.c3 || line1.c3
42             || line6.c4 || line5.c4 || line4.c4 || line3.c4 || line2.c4 || line1.c4
43             || line6.c5 || line5.c5 || line4.c5 || line3.c5 || line2.c5 || line1.c5
44             || line6.c6 || line5.c6 || line4.c6 || line3.c6 || line2.c6 || line1.c6 result6,
45     line1.c6 || line2.c6 || line3.c6 || line4.c6 || line5.c6 || line6.c6
46             || line1.c5 || line2.c5 || line3.c5 || line4.c5 || line5.c5 || line6.c5
47             || line1.c4 || line2.c4 || line3.c4 || line4.c4 || line5.c4 || line6.c4
48             || line1.c3 || line2.c3 || line3.c3 || line4.c3 || line5.c3 || line6.c3
49             || line1.c2 || line2.c2 || line3.c2 || line4.c2 || line5.c2 || line6.c2
50             || line1.c1 || line2.c1 || line3.c1 || line4.c1 || line5.c1 || line6.c1 result7,
51     line6.c6 || line5.c6 || line4.c6 || line3.c6 || line2.c6 || line1.c6
52             || line6.c5 || line5.c5 || line4.c5 || line3.c5 || line2.c5 || line1.c5
53             || line6.c4 || line5.c4 || line4.c4 || line3.c4 || line2.c4 || line1.c4
54             || line6.c3 || line5.c3 || line4.c3 || line3.c3 || line2.c3 || line1.c3
55             || line6.c2 || line5.c2 || line4.c2 || line3.c2 || line2.c2 || line1.c2
56             || line6.c1 || line5.c1 || line4.c1 || line3.c1 || line2.c1 || line1.c1 result8
57  from lines line1, lines line2, lines line3, lines line4, lines line5, lines line6
58  where line1.line + line2.line + line3.line + line4.line + line5.line + line6.line = 222222
59  and ltrim(line1.line + line2.line + line3.line, '012') is null
60  and ltrim(line1.line + line2.line + line3.line + line4.line, '012') is null
61  and ltrim(line1.line + line2.line + line3.line + line4.line + line5.line, '012') is null
62  and ltrim(to_char(line1.line + 10*line2.line + 100*line3.line + 1000*line4.line + 10000*line5.line + 100000*line6.line), '012') is null
63  and ltrim(to_char(line1.line + 10*line2.line + 100*line3.line), '012') is null
64  and ltrim(to_char(line1.line + 10*line2.line + 100*line3.line + 1000*line4.line), '012') is null
65  and ltrim(to_char(line1.line + 10*line2.line + 100*line3.line + 1000*line4.line + 10000*line5.line), '012') is null
66  and ltrim(to_char(100000*line1.line + 10000*line2.line + 1000*line3.line + 100*line4.line + 10*line5.line + line6.line), '012') is null
67  and ltrim(to_char(100*line1.line + 10*line2.line + line3.line), '012') is null
68  and ltrim(to_char(1000*line1.line + 100*line2.line + 10*line3.line + line4.line), '012') is null
69  and ltrim(to_char(10000*line1.line + 1000*line2.line + 100*line3.line + 10*line4.line + line5.line), '012') is null
70  )
71  select count(distinct greatest(result1, result2, result3, result4, result5, result6, result7, result8))
72  from result;

COUNT(DISTINCTGREATEST(RESULT1,RESULT2,RESULT3,RESULT4,RESULT5,RESULT6,RESULT7,RESULT8))
----------------------------------------------------------------------------------------
155

• 博文量
1954
• 访问量
10719113