SQL/with_group_lvl_data.sql at master · Testudinate/SQL · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
Ниже пример, который должен вывести 4 группы данных, которые необходимо
поместить в 1,2,3 и 4 столбец некоторого отчета соответственно,
но возникает небольшая проблемка с работой данного алгоритма,
когда количество записей 2, то данные разносятся в 1 и 3 столбец.

1. Пример ( некорректный вывод для 2 -ух записей )

with a as
  (select 1 p from dual union all
   select 22 p from dual --union all
   --select 4 p from dual union all
   --select 44 p from dual union all
   --select 2 p from dual union all
   --select 16 p from dual union all
   --select 7 p from dual union all
   --select 99 p from dual union all
   --select 100 p from dual
   ),
   b as (select rownum row_cnt , a.p from a)
select ROW_CNT ROW_CNT2,p,1 lvl_1,lvl,ROW_CNT
  from (
         select ROW_CNT,p,2 lvl
           from b where b.row_cnt <= (select ceil(max(row_cnt)/2) max_row_cnt from b)
         union all
         select ROW_CNT,p,4 lvl
           from b where b.row_cnt > (select ceil(max(row_cnt)/2) max_row_cnt from b)
       ) sub1
where sub1.lvl = 2
  and sub1.row_cnt <= (select ceil(max(row_cnt)/2) max_row_cnt
                         from ( select ROW_CNT,p,2 lvl from b
                                 where b.row_cnt <= (select ceil(max(row_cnt)/2) max_row_cnt from b)
                              )
                       )
 union all
 select ROW_CNT ROW_CNT2,p,2 lvl_1,lvl,ROW_CNT
  from (
         select ROW_CNT,p,2 lvl
           from b where b.row_cnt <= (select ceil(max(row_cnt)/2) max_row_cnt from b)
         union all
         select ROW_CNT,p,4 lvl
           from b where b.row_cnt > (select ceil(max(row_cnt)/2) max_row_cnt from b)
       ) sub1
where sub1.lvl = 2
  and sub1.row_cnt > (select ceil(max(row_cnt)/2) max_row_cnt
                         from ( select ROW_CNT,p,2 lvl from b
                                 where b.row_cnt <= (select ceil(max(row_cnt)/2) max_row_cnt from b)
                              )
                       )


  union all
 select ROW_CNT2,p,3 lvl_1,lvl,ROW_CNT
 from
 (select ROWNUM ROW_CNT2,p,3 lvl_1,lvl,ROW_CNT
  from (
         select ROW_CNT,p,4 lvl
           from b where b.row_cnt > (select ceil(max(row_cnt)/2) max_row_cnt from b)
       ) sub1
where sub1.lvl = 4) s1
where  ROW_CNT2 <= (select ceil(max(row_cnt)/2) max_row_cnt  --2
                         from  ( select ROWNUM ROW_CNT,p,4 lvl
                               from b where b.row_cnt > (select ceil(max(row_cnt)/2) max_row_cnt from b)
                               ) sub2
                    )
  union all
  select ROW_CNT2,p,4 lvl_1,lvl,ROW_CNT
 from
 (select ROWNUM ROW_CNT2,p,3 lvl_1,lvl,ROW_CNT
  from (
         select ROW_CNT,p,4 lvl
           from b where b.row_cnt > (select ceil(max(row_cnt)/2) max_row_cnt from b)
       ) sub1
where sub1.lvl = 4) s2
where ROW_CNT2 > (select ceil(max(row_cnt)/2) max_row_cnt  --2
                         from  ( select ROWNUM ROW_CNT,p,4 lvl
                               from b where b.row_cnt > (select ceil(max(row_cnt)/2) max_row_cnt from b)
                               ) sub2
                       )

2. Решение для 2 - ух записей ( оно короче )
with a as
  (select 1 p from dual union all
   select 22 p from dual union all
   select 4 p from dual union all
   select 44 p from dual union all
   select 2 p from dual union all
   select 16 p from dual union all
   select 7 p from dual union all
   select 99 p from dual union all
   select 100 p from dual
   ),
   b as (select rownum row_cnt , a.p from a)
  select * from
(select b.p, ntile(4) over(order by row_cnt) title from b
) --where title =2