情况如下:
数据量在50W行甚至更多
数据的形式如下列1 列2 列3 列4 列5
A B C D E
A B D E F
A B F C D现在想写个sql
实现查询出来的结果如下第一行按照 列1分组的数据
第二行至第N行 按照 列1,列2分组的数据
第N+1行至N+N行显示 按照 列1,列2,列3分组的数据
以此类推
最后希望能够多生成1列,来记录数据之间的父子关系
如下
列6
1
1.1
1.2
1.1.1
1.1.2
1.2.1
1.2.2谢谢大家了万分感谢
数据量在50W行甚至更多
数据的形式如下列1 列2 列3 列4 列5
A B C D E
A B D E F
A B F C D现在想写个sql
实现查询出来的结果如下第一行按照 列1分组的数据
第二行至第N行 按照 列1,列2分组的数据
第N+1行至N+N行显示 按照 列1,列2,列3分组的数据
以此类推
最后希望能够多生成1列,来记录数据之间的父子关系
如下
列6
1
1.1
1.2
1.1.1
1.1.2
1.2.1
1.2.2谢谢大家了万分感谢
A B C D E
第二行至第N行 按照 列1,列2分组的数据
A B C D E
第N+1行至N+N行显示 按照 列1,列2,列3分组的数据
A B C D E
A B D E F
A B F C D 也就是原来的4条记录变成了5条
A B C D E
A B D E F
A B F C D 现在要写一个sql语句查询该表,得到以下的检索结果列1 列2 列3 列4 列5 列6
A B C D E 1
A B C D E 1.1
A B C D E 1.1.1
A B D E F 1.1.2
A B F C D 1.1.3
A B C D E 1.1.1.1
A B D E F 1.1.2.1
A B F C D 1.1.3.1第一行按照 列1进行分组得到的数据
第二行 按照 列1,列2分组得到的数据
第3行至5行显示 按照 列1,列2,列3分组的数据
第6行至8行是不进行分组的数据
而列6相当於标志位一样,用以说明这几行数据之间的父子关系
A E F C D ??那1.?
from table1 t
group by t.col1,t.col2,t.col3
),
b as (
select t.col1,t.col2,null col3,sum(t.col4) col4,null t3, row_number() over(partition by t.col1 order by t.col2) t4
from a t
group by t.col1,t.col2
),
c as (
select t.col1,null col2,null col3,sum(t.col4) col4,null t3, null t4,row_number() over(order by t.col1) t5
from b t
group by t.col1
)
select c.t5,b.t4,a.t3,null t2 ,c.t5||'.'||b.t4||'.'||a.t3 col6 ,a.col1,a.col2,a.col3,a.col4 from c,a,b where c.col1=a.col1 and b.col1=a.col1 and b.col2=a.col2
union
select c.t5,b.t4,b.t3,null t2,c.t5||'.'||b.t4 col6,b.col1,b.col2,b.col3,b.col4 from c,b where b.col1=c.col1
union
select c.t5,c.t4,c.t3,null t2, c.t5||'' col6,c.col1,c.col2,c.col3,c.col4 from c
union
select c.t5,b.t4,a.t3,row_number() over(partition by t.col1,t.col2 ,t.col3 order by rownum) t2,
c.t5||'.'||b.t4||'.'||a.t3||'.'||row_number() over(partition by t.col1,t.col2 ,t.col3 order by rownum) col6,
t.col1,t.col2,t.col3,t.col4 from table1 t ,c,a,b where c.col1=t.col1 and b.col1=t.col1 and b.col2=t.col2 and a.col1=t.col1 and a.col2=t.col2 and a.col3=t.col3
select *
from (
select t.col1,
t.col2,
t.col3,
t.rownum_01,
t.t4,
case when t.col2 is not null then t5
else 0
end t5,
case when t.col3 is not null then t6
else 0
end t6,
case when t.rownum_01 is not null then t7
else 0
end t7,
t.t4||case when t.col2 is not null then '.'||t5
else null
end
||case when t.col3 is not null then '.'||t6
else null
end
||case when t.rownum_01 is not null then '.'||t7
else null
end t8,
t.col4
from (
select t.col1,
t.col2,
t.col3,
rownum rownum_01,
sum(t.col4) col4 ,
dense_rank() over( order by t.col1) t4,
dense_rank() over(partition by t.col1 order by t.col2 ) t5,
dense_rank() over(partition by t.col1,t.col2 order by t.col3 ) t6,
row_number() over(partition by t.col1,t.col2 ,t.col3 order by rownum ) t7
from table1 t
group by grouping sets(
(t.col1),
(t.col1,t.col2),
(t.col1,t.col2,t.col3),
(t.col1,t.col2,t.col3,rownum)
)
)t
)t
order by t4,t5,t6,t7
是一个页面 可以实现在list中显示成树形结构的数据.
create table t(x1 varchar2(1),x2 varchar2(1),x3 varchar2(1),x4 varchar2(1),x5 varchar2(1));insert into T (X1, X2, X3, X4, X5)
values ('A', 'B', 'C', 'D', 'E');
insert into T (X1, X2, X3, X4, X5)
values ('A', 'B', 'D', 'E', 'F');
insert into T (X1, X2, X3, X4, X5)
values ('A', 'B', 'F', 'C', 'D');
commit;SQL> select * from t;
X1 X2 X3 X4 X5
-- -- -- -- --
A B C D E
A B D E F
A B F C D--SQL 1
SELECT a.*,r,substr(c,1,instr(c,'.',1,r)-1) x
FROM (SELECT t.*,
dense_rank() over(ORDER BY x1) ||'.'||
dense_rank() over(PARTITION BY x1 ORDER BY x2) ||'.'||
dense_rank() over(PARTITION BY x1,x2 ORDER BY x3) ||'.'||
dense_rank() over(PARTITION BY x1,x2,x3 ORDER BY x4,x5)||'.' c
FROM t) a,(SELECT ROWNUM r FROM dual CONNECT BY ROWNUM<=4) b
WHERE translate(substr(c,instr(c,'.',1,r)+1),'*1.','*') IS NULL;X1 X2 X3 X4 X5 C R X
-- -- -- -- -- --------------- - ---------------
A B C D E 1.1.1.1. 1 1
A B C D E 1.1.1.1. 2 1.1
A B C D E 1.1.1.1. 3 1.1.1
A B D E F 1.1.2.1. 3 1.1.2
A B F C D 1.1.3.1. 3 1.1.3
A B C D E 1.1.1.1. 4 1.1.1.1
A B D E F 1.1.2.1. 4 1.1.2.1
A B F C D 1.1.3.1. 4 1.1.3.1--SQL 2
SELECT * FROM (
SELECT a.*,r,substr(c,1,instr(c,'.',1,r)-1) x,
dense_rank() over(PARTITION BY r,substr(c,1,instr(c,'.',1,r)-1) ORDER BY c) r1
FROM (SELECT t.*,
dense_rank() over(ORDER BY x1) ||'.'||
dense_rank() over(PARTITION BY x1 ORDER BY x2) ||'.'||
dense_rank() over(PARTITION BY x1,x2 ORDER BY x3) ||'.'||
dense_rank() over(PARTITION BY x1,x2,x3 ORDER BY x4,x5)||'.' c
FROM t) a,(SELECT ROWNUM r FROM dual CONNECT BY ROWNUM<=4) b
) WHERE r1=1;
X1 X2 X3 X4 X5 C R X
-- -- -- -- -- --------------- - ---------------
A B C D E 1.1.1.1. 1 1
A B C D E 1.1.1.1. 2 1.1
A B C D E 1.1.1.1. 3 1.1.1
A B D E F 1.1.2.1. 3 1.1.2
A B F C D 1.1.3.1. 3 1.1.3
A B C D E 1.1.1.1. 4 1.1.1.1
A B D E F 1.1.2.1. 4 1.1.2.1
A B F C D 1.1.3.1. 4 1.1.3.1