根据用户输入范围 将记录分类的问题

Question

各位大侠好。小弟最近赶项目焦头烂额。
遇到一棘手问题,还请各位路见不平,答贴相助。首先附上测试数据:
create table test1
(col_site varchar2(10),
col_well varchar2(10),
col1 number,
col2 number,
col3 number);insert into test1 values('A','w1',10,32,1);
insert into test1 values('A','w2',20,22,4);
insert into test1 values('A','w3',30,15,5);
insert into test1 values('A','w4',15,25,7);
insert into test1 values('A','w5',18,35,8);
insert into test1 values('A','w6',25,45,10);
insert into test1 values('A','w7',32,55,2);
insert into test1 values('A','w8',35,65,8);
insert into test1 values('A','w9',40,75,9);
需求如下：
1、用户在test1表的col1、col2、col3 三列中任选一列(例如选择列col1)
2、用户自定义几个范围标准(例如下四个范围)
col1<=10   10<col1<=20   20<col1<=30  col1>303、test1表 按col_site分组，将 col_well、col2、col3，按第2点的范围分类。
期望的效果如下图1：小弟才疏识浅，下面是我的做法及疑问：
1、自定义函数
create or replace function test_func1(sel_col in varchar2) return number is
  Result number;
  sql_str varchar2(4000);
begin
sql_str := 'select case when '||sel_col||' <= 10 then 1 '||
 ' when '||sel_col||' > 10 and '||sel_col||' <=20 then 2 '||
 ' when '||sel_col||' > 20 and '||sel_col||' <=30 then 3 '||
 ' when '||sel_col||' > 30 then 4 '||
'end seq_no from dual';
  dbms_output.put_line(sql_str);
  execute immediate sql_str into Result;
  return(Result);
end test_func1;
2、使用decode进行分类
select col_site,
decode(test_func1(test1.col1),1,col_well)  col_well_1,
decode(test_func1(test1.col1),1,col2)  col2_1,
decode(test_func1(test1.col1),1,col3)  col3_1,
decode(test_func1(test1.col1),2,col_well)  col_well_2,
decode(test_func1(test1.col1),2,col2)  col2_2,
decode(test_func1(test1.col1),2,col3)  col3_2,
decode(test_func1(test1.col1),3,col_well)  col_well_3,
decode(test_func1(test1.col1),3,col2)  col2_3,
decode(test_func1(test1.col1),3,col3)  col3_3,
decode(test_func1(test1.col1),4,col_well)  col_well_4,
decode(test_func1(test1.col1),4,col2)  col2_4,
decode(test_func1(test1.col1),4,col3)  col3_4
from test1;运行结果如下图2：3、问题：
请教大虾们，如何将空的单元格去除，达到 图1的效果？

Answer 1

WITH a AS
 (SELECT row_number() over(PARTITION BY col_site ORDER BY col_well_1) rn, t.*
    FROM (SELECT col_site,
                 decode(test_func1(test1.col1), 1, col_well) col_well_1,
                 decode(test_func1(test1.col1), 1, col2) col2_1,
                 decode(test_func1(test1.col1), 1, col3) col3_1
            FROM test1) t
   WHERE col_well_1 IS NOT NULL),
b AS
 (SELECT row_number() over(PARTITION BY col_site ORDER BY col_well_2) rn, t.*
    FROM (SELECT col_site,
                 decode(test_func1(test1.col1), 2, col_well) col_well_2,
                 decode(test_func1(test1.col1), 2, col2) col2_2,
                 decode(test_func1(test1.col1), 2, col3) col3_2
            FROM test1) t
   WHERE col_well_2 IS NOT NULL),
c AS
 (SELECT row_number() over(PARTITION BY col_site ORDER BY col_well_3) rn, t.*
    FROM (SELECT col_site,
                 decode(test_func1(test1.col1), 3, col_well) col_well_3,
                 decode(test_func1(test1.col1), 3, col2) col2_3,
                 decode(test_func1(test1.col1), 3, col3) col3_3
            FROM test1) t
   WHERE col_well_3 IS NOT NULL),
d AS
 (SELECT row_number() over(PARTITION BY col_site ORDER BY col_well_4) rn, t.*
    FROM (SELECT col_site,
                 decode(test_func1(test1.col1), 4, col_well) col_well_4,
                 decode(test_func1(test1.col1), 4, col2) col2_4,
                 decode(test_func1(test1.col1), 4, col3) col3_4
            FROM test1) t
   WHERE col_well_4 IS NOT NULL)
SELECT nvl(n.COL_SITE, d.col_site) COL_SITE,
       COL_WELL_1,
       COL2_1,
       COL3_1,
       COL_WELL_2,
       COL2_2,
       COL3_2,
       COL_WELL_3,
       COL2_3,
       COL3_3,
       COL_WELL_4,
       COL2_4,
       COL3_4
  FROM (SELECT nvl(m.rn, c.rn) rn,
               nvl(m.COL_SITE, c.col_site) COL_SITE,
               COL_WELL_1,
               COL2_1,
               COL3_1,
               COL_WELL_2,
               COL2_2,
               COL3_2,
               COL_WELL_3,
               COL2_3,
               COL3_3
          FROM (SELECT nvl(a.rn, b.rn) rn,
                       nvl(a.COL_SITE, b.col_site) COL_SITE,
                       COL_WELL_1,
                       COL2_1,
                       COL3_1,
                       COL_WELL_2,
                       COL2_2,
                       COL3_2
                  FROM a
                  FULL OUTER JOIN b
                    ON (a.rn = b.rn AND a.COL_SITE = b.COL_SITE)) m
          FULL OUTER JOIN c
            ON (m.rn = c.rn AND m.COL_SITE = c.COL_SITE)) n
  FULL OUTER JOIN d
    ON (n.rn = d.rn AND n.COL_SITE = d.COL_SITE)

Answer 2

我理想的解决方法是：select ... max(decode()) from ... group by 这样的形式，但max在一个分组内只能取一条记录，十分困惑。
再次感谢回帖的兄弟！

Answer 3

真是惭愧，小弟之前问过类似的帖子。其实思路都是一样的，自己没搞清楚。
现附上自己写的sql ，请大家看看有什么改进的地方。select
col_site,g_no,
max(decode(seq_no,1,col_well)) cw1,
max(decode(seq_no,1,col1)) c1_1,
max(decode(seq_no,1,col2)) c2_1,
max(decode(seq_no,2,col_well)) cw2,
max(decode(seq_no,2,col1)) c1_2,
max(decode(seq_no,2,col2)) c2_2,
max(decode(seq_no,3,col_well)) cw3,
max(decode(seq_no,3,col1)) c1_3,
max(decode(seq_no,3,col2)) c2_3,
max(decode(seq_no,4,col_well)) cw4,
max(decode(seq_no,4,col1)) c1_4,
max(decode(seq_no,4,col2)) c2_4
from 
(select 
col_site,col_well,col1,col2,col3,seq_no,
row_number() over (partition by col_site,seq_no order by col1) g_no
from 
(select a.*,test_func1(a.col1) seq_no from test1 a))
group by col_site,g_no;

Answer 4

把你的三个区分列拆分成三个虚表（表中以code_site所谓关联的id）,然后每两个表做全连接，然后去重复。

Answer 5

SQL> with tb as
  2  (select COL_SITE,rownum rn from test1),
  3  tb1 as
  4  (select COL_SITE,COL_WELL||' '||col1||' '||col2||' '||col3 str1,rownum rn
  5  from test1 where   col1<=10),
  6  tb2 as
  7  (select COL_SITE,COL_WELL||' '||col1||' '||col2||' '||col3  str2,rownum rn
  8  from test1 where  (col1>10 and col1<=20)),
  9  tb3 as
 10  (select COL_SITE,COL_WELL||' '||col1||' '||col2||' '||col3 str3,rownum rn
 11  from test1 where col1>20 and col1<=30),
 12  tb4 as
 13  (select COL_SITE,COL_WELL||' '||col1||' '||col2||' '||col3 str4,rownum rn
 14  from test1 where col1>30)
 15  select k.COL_SITE,a.str1,b.str2,c.str3,d.str4
 16  from tb k left join tb1 a on k.COL_SITE=a.COL_SITE and k.rn=a.rn
 17  left join tb2 b on k.COL_SITE=b.COL_SITE and k.rn=b.rn
 18  left join tb3 c on k.COL_SITE=c.COL_SITE and k.rn=c.rn
 19  left join tb4 d on k.COL_SITE=d.COL_SITE and k.rn=d.rn
 20  where k.rn<=(
 21  select greatest(max(a.rn),max(b.rn),max(c.rn),max(d.rn))
 22  from tb1 a,tb2 b,tb3 c,tb4 d)
 23  /
 
COL_SITE   STR1                                                                             STR2                                                                             STR3                                                                             STR4
---------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- --------------------------------------------------------------------------------
A          w1 10 32 1                                                                       w2 20 22 4                                                                       w3 30 15 5                                                                       w7 32 55 2
A                                                                                           w4 15 25 7                                                                       w6 25 45 10                                                                      w8 35 65 8
A    

Answer 6

SQL> with tb as
  2  (select COL_SITE,rownum rn from test1),
  3  tb1 as
  4  (select COL_SITE,COL_WELL||' '||col1||' '||col2||' '||col3 str1,rownum rn
  5  from test1 where   col1<=10),
  6  tb2 as
  7  (select COL_SITE,COL_WELL||' '||col1||' '||col2||' '||col3  str2,rownum rn
  8  from test1 where  (col1>10 and col1<=20)),
  9  tb3 as
 10  (select COL_SITE,COL_WELL||' '||col1||' '||col2||' '||col3 str3,rownum rn
 11  from test1 where col1>20 and col1<=30),
 12  tb4 as
 13  (select COL_SITE,COL_WELL||' '||col1||' '||col2||' '||col3 str4,rownum rn
 14  from test1 where col1>30)
 15  select k.COL_SITE,a.str1,b.str2,c.str3,d.str4
 16  from tb k left join tb1 a on k.COL_SITE=a.COL_SITE and k.rn=a.rn
 17  left join tb2 b on k.COL_SITE=b.COL_SITE and k.rn=b.rn
 18  left join tb3 c on k.COL_SITE=c.COL_SITE and k.rn=c.rn
 19  left join tb4 d on k.COL_SITE=d.COL_SITE and k.rn=d.rn
 20  where k.rn<=(
 21  select greatest(max(a.rn),max(b.rn),max(c.rn),max(d.rn))
 22  from tb1 a,tb2 b,tb3 c,tb4 d)
 23  /
 
COL_SITE   STR1                                                                             STR2                                                                             STR3                                                                             STR4
---------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- --------------------------------------------------------------------------------
A          w1 10 32 1                                                                       w2 20 22 4                                                                       w3 30 15 5                                                                       w7 32 55 2
A                                                                                           w4 15 25 7                                                                       w6 25 45 10                                                                      w8 35 65 8
A                                                                                           w5 18 35 8                                                                                                                                                        w9 40 75 9
 
SQL> 

Answer 7

--代码
with tb as
(select COL_SITE,rownum rn from test1),
tb1 as
(select COL_SITE,COL_WELL,col1,col2,col3,rownum rn 
from test1 where   col1<=10),
tb2 as
(select COL_SITE,COL_WELL,col1,col2,col3,rownum rn 
from test1 where  (col1>10 and col1<=20)),
tb3 as
(select COL_SITE,COL_WELL,col1,col2,col3,rownum rn 
from test1 where col1>20 and col1<=30),
tb4 as
(select COL_SITE,COL_WELL,col1,col2,col3,rownum rn 
from test1 where col1>30)
select k.COL_SITE,a.COL_WELL,a.col1,a.col2,a.col3,
b.COL_WELL,b.col1,b.col2,b.col3,c.COL_WELL,c.col1,c.col2,c.col3,
d.COL_WELL,d.col1,d.col2,d.col3
from tb k left join tb1 a on k.COL_SITE=a.COL_SITE and k.rn=a.rn
left join tb2 b on k.COL_SITE=b.COL_SITE and k.rn=b.rn
left join tb3 c on k.COL_SITE=c.COL_SITE and k.rn=c.rn
left join tb4 d on k.COL_SITE=d.COL_SITE and k.rn=d.rn
where k.rn<=(
select greatest(max(a.rn),max(b.rn),max(c.rn),max(d.rn))
from tb1 a,tb2 b,tb3 c,tb4 d)--测试显示你的结果
SQL> 
SQL> with tb as
  2  (select COL_SITE,rownum rn from test1),
  3  tb1 as
  4  (select COL_SITE,COL_WELL,col1,col2,col3,rownum rn
  5  from test1 where   col1<=10),
  6  tb2 as
  7  (select COL_SITE,COL_WELL,col1,col2,col3,rownum rn
  8  from test1 where  (col1>10 and col1<=20)),
  9  tb3 as
 10  (select COL_SITE,COL_WELL,col1,col2,col3,rownum rn
 11  from test1 where col1>20 and col1<=30),
 12  tb4 as
 13  (select COL_SITE,COL_WELL,col1,col2,col3,rownum rn
 14  from test1 where col1>30)
 15  select k.COL_SITE,a.COL_WELL,a.col1,a.col2,a.col3,
 16  b.COL_WELL,b.col1,b.col2,b.col3,c.COL_WELL,c.col1,c.col2,c.col3,
 17  d.COL_WELL,d.col1,d.col2,d.col3
 18  from tb k left join tb1 a on k.COL_SITE=a.COL_SITE and k.rn=a.rn
 19  left join tb2 b on k.COL_SITE=b.COL_SITE and k.rn=b.rn
 20  left join tb3 c on k.COL_SITE=c.COL_SITE and k.rn=c.rn
 21  left join tb4 d on k.COL_SITE=d.COL_SITE and k.rn=d.rn
 22  where k.rn<=(
 23  select greatest(max(a.rn),max(b.rn),max(c.rn),max(d.rn))
 24  from tb1 a,tb2 b,tb3 c,tb4 d)
 25  /
 
COL_SITE   COL_WELL         COL1       COL2       COL3 COL_WELL         COL1       COL2       COL3 COL_WELL         COL1       COL2       COL3 COL_WELL         COL1       COL2       COL3
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
A          w1                 10         32          1 w2                 20         22          4 w3                 30         15          5 w7                 32         55          2
A                                                      w4                 15         25          7 w6                 25         45         10 w8                 35         65          8
A                                                      w5                 18         35          8                                             w9                 40         75          9
 
SQL>