复杂SQL: 如何在某列最大行数的情况下，压缩大于最大行数的其它列的null值和重复值？

显示全部楼层 · 2012-10-9 18:05:37

高难度SQL：如何在某列最大行数的情况下，压缩大于最大行数的其它列的null值？

原表t_list的数据如下：
p_no p_nametype_Atype_B type_C
-----------------------------------------------------------
1 产品1 A11B11

1 产品1
B12
1 产品1 A11
1 产品1
C11
1 产品1 A12
2 产品2
B22
C21

2 产品2 A21B21
C21
2 产品2
B22
C22
2 产品2
B23

2 产品2 A22B24

3 产品3 A31B31
C31
3 产品3 A32

3 产品3
C31

3 产品3 A33
C32
产品1的type_A、type_B和type_C中有数值的最大行数为2，
则在保持最大行数的情况下，把其它列大于最大行数的的null值去掉，
即产品1的记录变为：
p_no p_nametype_Atype_B type_C
-----------------------------------------------------------
1 产品1 A11 B11
C11
1 产品1 A12 B12

根据以上转换规则，请问：如何把表t_list变成：
p_no p_nametype_Atype_B type_C
-----------------------------------------------------------
1 产品1 A11 B11
C11
1 产品1 A12 B12

2 产品2 A21 B21
C21
2 产品2 A22 B22
C22
2 产品2
B23

2 产品2
B24

3 产品3 A31 B31
C31
3 产品3 A32
C32
3 产品3 A33

请大侠指点，小菜在此先谢了!
[ 本帖最后由 anyoneokay 于 2007-11-26 14:57 编辑 ]

千问 · 2012-10-9 18:05:37

补充建表SQL语句：
create table t_list
(
p_nonumber,

p_namevarchar2(50),

type_A varchar2(50),

type_B varchar2(50),

type_C varchar2(50)
);
insert into t_list values(1,'产品1','A11','B11', null);
insert into t_list values(1,'产品1', null,'B12', null);
insert into t_list values(1,'产品1','A11', null, null);
insert into t_list values(1,'产品1', null, null,'C11');
insert into t_list values(1,'产品1','A12', null, null);
insert into t_list values(2,'产品2', null,'B22', null);
insert into t_list values(2,'产品2','A21','B21','C21');
insert into t_list values(2,'产品2', null,'B22','C22');
insert into t_list values(2,'产品2', null,'B23', null);
insert into t_list values(2,'产品2','A22','B24', null);
insert into t_list values(3,'产品3','A31','B31','C31');
insert into t_list values(3,'产品3','A32', null, null);
insert into t_list values(3,'产品3', null, null,'C31');
insert into t_list values(3,'产品3','A33', null,'C32');
commit;

千问 · 2012-10-9 18:05:37

若无重复值可用如下语句，但在有重复值的情况下则不行：
select * from
(
select t.p_no,
p_name,
decode(ma,lag(ma, 1) over(partition by p_no order by type_a), null, ma) a,
decode(mb,lag(mb, 1) over(partition by p_no order by type_a), null, mb) b,
decode(mc,lag(mc, 1) over(partition by p_no order by type_a), null, mc) c
from
(
select t.*,

max(t.type_a) over(partition by p_no order by type_a) ma,

max(t.type_b) over(partition by p_no order by type_b) mb,

max(t.type_c) over(partition by p_no order by type_c) mc
from (select * from t_list) t
order by p_no, type_a, type_b, type_c
) t
) t
where nvl(a, '0') != '0'
or nvl(b, '0') != '0'
or nvl(c, '0') != '0'

千问 · 2012-10-9 18:05:37

咦，这个问题看着好眼熟~~~~
hehe，既然你的结果集没变，那在读取数据源的时候处理一下，过滤掉重复的列记录不就行了吗~~

千问 · 2012-10-9 18:05:37

[PHP]
如下：
SQL> select * from t_list;
P_NO P_NAME TYPE_A TYPE_B TYPE_C
---------- ---------- ------ ------ ------
1 产品1A11B11
1 产品1
B12
1 产品1A11
1 产品1
C11
1 产品1A12
2 产品2
B22
2 产品2A21B21C21
2 产品2
B22C22
2 产品2
B23
2 产品2A22B24
3 产品3A31B31C31
3 产品3A32
3 产品3
C31
3 产品3A33 C32
14 rows selected
SQL>
SQL> select p_no,
2 p_name,
3 type_a,
4 type_b,
5 type_c
6from
7(
8selectp_no,
9
p_name,
10
decode(ma,lag(ma, 1) over(partition by p_no order by ma), null, ma) type_a,
11
decode(mb,lag(mb, 1) over(partition by p_no order by mb), null, mb) type_b,
12
decode(mc,lag(mc, 1) over(partition by p_no order by mc), null, mc) type_c
13from
14(selectp_no,
15
p_name,
16
max(ma) over(partition by p_no order by ma) ma,
17
max(mb) over(partition by p_no order by mb) mb,
18
max(mc) over(partition by p_no order by mc) mc
19from
20(select p_no,
21
p_name,
22
decode(rn1, 1, type_a, null, type_a, null) ma,
23
decode(rn2, 1, type_b, null, type_b, null) mb,
24
decode(rn3, 1, type_c, null, type_c, null) mc
25from
26(selectp_no,
27 p_name,
28 type_a,
29 type_b,
30 type_c,
31 decode(type_a, null, null, row_number() over(partition by p_no, type_a order by rownum)) rn1,
32 decode(type_b, null, null, row_number() over(partition by p_no, type_b order by rownum)) rn2,
33 decode(type_c, null, null, row_number() over(partition by p_no, type_c order by rownum)) rn3
34fromt_list))))
35where type_a is not null
36 or type_b is not null
37 or type_c is not null;
P_NO P_NAME TYPE_A TYPE_B TYPE_C
---------- ---------- ------ ------ ------
1 产品1A11B11C11
1 产品1A12B12
2 产品2A21B21C21
2 产品2A22B22
2 产品2
B23
2 产品2
B24C22
3 产品3A31B31C31
3 产品3A32 C32
3 产品3A33
9 rows selected
SQL>
[/PHP]

千问 · 2012-10-9 18:05:37

SQL> select * from t_list;

P_NO P_NAME
TYPE_A TYPE_B TYPE_C
---------- -------------------- ---------- ---------- ----------
1 产品1
A11B11
1 产品1
B12
1 产品1
A11

1 产品1
C11
1 产品1
A12

2 产品2
B22
2 产品2
A21B21C21
2 产品2
B22C22
2 产品2
B23
2 产品2
A22B24
3 产品3
A31B31C31
3 产品3
A32

3 产品3
C31
3 产品3
A33
C32

14 rows selected

SQL>
SQL> select * from
2(
3select t.p_no,
4 t.p_name,
5 decode(ma,lag(ma, 1) over(partition by p_no order by type_a), null, ma) a,
6 decode(mb,lag(mb, 1) over(partition by p_no order by type_a), null, mb) b,
7 decode(mc,lag(mc, 1) over(partition by p_no order by type_a), null, mc) c
8from
9(
10 select t.*,
11
max(t.type_a) over(partition by p_no order by type_a) ma,
12
max(t.type_b) over(partition by p_no order by type_b) mb,
13
max(t.type_c) over(partition by p_no order by type_c) mc
14 from (
15
select *
16
from (SELECT p_no,
17
p_name,
18
decode(row_number() OVER(partition by p_no, type_a order by type_a),1,type_a,null) type_a,
19
decode(row_number() OVER(partition by p_no, type_b order by type_b),1,type_b,null) type_b,
20
decode(row_number() OVER(partition by p_no, type_c order by type_c),1,type_c,null) type_c
21
FROM t_list) t
22
where nvl(type_a, '0') != '0'
23
or nvl(type_b, '0') != '0'
24
or nvl(type_c, '0') != '0'
25
) t
26 order by p_no
27) t
28) t
29where nvl(a, '0') != '0'
30or nvl(b, '0') != '0'
31or nvl(c, '0') != '0'
32/

P_NO P_NAME
A
B
C
---------- -------------------- ---------- ---------- ----------
1 产品1
A11B11C11
1 产品1
A12B12
2 产品2
A21B21C21
2 产品2
A22B24C22
2 产品2
B23
2 产品2
B22
3 产品3
A31B31C31
3 产品3
A32
C32
3 产品3
A33

9 rows selected

SQL>

千问 · 2012-10-9 18:05:37

ls的太强了

千问 · 2012-10-9 18:05:37

[PHP]

LZ，实际上你要的结果是由规律的！
如果是如下的要求，该如何实现呢？

SQL> select * from t_list;
P_NO P_NAME TYPE_A TYPE_B TYPE_C
---------- ---------- ------ ------ ------
1 产品1A21B12
1 产品1
B11
1 产品1
C11
1 产品1A11

2 产品2
B28C66
2 产品2A33B22C21
2 产品2A23B27
2 产品2
B21

3 产品3
B31
3 产品3

3 产品3AA88 B6 C99
3 产品3

3 产品3AA5B8 C77
3 产品3
C3
14 rows selected
SQL>

结果记录变为如下：
P_NO P_NAME TYPE_A TYPE_B TYPE_C
---------- ---------- ------ ------ ------
1 产品1A21B12C11
1 产品1A11B11
2 产品2A33B28C66
2 产品2A23B22C21
2 产品2
B27
2 产品2
B21
3 产品3AA88 B31C99
3 产品3AA5B6 C77
3 产品3
B8 C3
9 rows selected
SQL>
[/PHP]
[ 本帖最后由 bell6248 于 2007-11-26 16:05 编辑 ]

千问 · 2012-10-9 18:05:37

谢谢junsansi和bell6248，
你们对分析函数应用自如，偶十分佩服！

千问 · 2012-10-9 18:05:37

原帖由 bell6248 于 2007-11-26 16:03 发表
[PHP]

LZ，实际上你要的结果是由规律的！
如果是如下的要求，该如何实现呢？

SQL> select * from t_list;
P_NO P_NAME TYPE_A TYPE_B TYPE_C
---------- ---------- ------ ------...

有点儿意思~~
按照我前面回复语句中的思路肯定能解决这个问题，不过就是语句会写的更加复制一些~~~