我补充一下:注意:COL2是2的记录根据顺序不一定是在一个组的。 不要给我SELECT COL2,COUNT(1) FROM TAB GROUP BY COL2 ORDER BY COL1
如果col1不是连续,就要用rownum,明天研究一下
select COL2,count(*) from ( select L.*, ROW_NUMBER() OVER ( PARTITION BY COL2 ORDER BY COL1 DESC ) + COL1 AS GGM FROM ( SELECT * from test_c t ORDER BY T.COL1 ) L ) A group by A.COL2,GGM order by min(A.COL1)
呵呵,不好意思,写麻烦了select COL2,count(*) from ( select L.*, ROW_NUMBER() OVER ( PARTITION BY COL2 ORDER BY COL1 DESC ) + COL1 AS GGM FROM test_c L ) A group by A.COL2,GGM order by min(A.COL1)
create table tb (col1 int,col2 int); insert into tb values(1,2); insert into tb values(2,2); insert into tb values(3,2); insert into tb values(4,9); insert into tb values(5,9); insert into tb values(6,2); insert into tb values(7,2); insert into tb values(8,3);with t as (select t1.col1,t1.col2,ROW_NUMBER()over(partition by col2 order by col1) as rn from tb t1) select t.col2,COUNT(1) CNT from t group by t.col2,t.col1-rn order by min(col1) COL2 CNT 1 2 3 2 9 2 3 2 2 4 3 1
SQL> with tmp as( 2 select 1 col1, 2 col2 from dual union all 3 select 2 col1, 2 col2 from dual union all 4 select 3 col1, 2 col2 from dual union all 5 select 4 col1, 9 col2 from dual union all 6 select 5 col1, 9 col2 from dual union all 7 select 6 col1, 2 col2 from dual union all 8 select 7 col1, 2 col2 from dual union all 9 select 8 col1, 3 col2 from dual 10 ) 11 select col2, 12 nvl(lead(col1) over(order by col1) - col1, 1) cnt --这里依赖于col1是连续的,若不连续则需用rownum替换col1 13 from ( 14 select col1, col2, 15 lag(col1) over(partition by col2 order by col1) lag_col1 16 from tmp 17 ) 18 where lag_col1 is null 19 or col1 - lag_col1 <> 1 20 order by col1 21 /
COL2 CNT ---------- ---------- 2 3 9 2 2 2 3 1
with tt as (select 1 col1, 2 col2 from dual union all select 2 col1, 2 col2 from dual union all select 3 col1, 2 col2 from dual union all select 4 col1, 9 col2 from dual union all select 5 col1, 9 col2 from dual union all select 6 col1, 2 col2 from dual union all select 7 col1, 2 col2 from dual union all select 8 col1, 3 col2 from dual)SELECT col2, COUNT(*) cnt FROM (SELECT col1, col2, lag(col2, 1, -1) over(ORDER BY col1) pcol2 FROM tt) a START WITH col2 <> pcol2 CONNECT BY PRIOR col1 = col1 - 1 AND col2 = PRIOR col2 GROUP BY col2, col1 - LEVEL ORDER BY col1 - LEVEL
SQL> select b.col2 col2,count(*) cn 2 from 3 (select a.col1,a.col2,a.col1+a.col2-rownum rn from 4 (select col1,col2 from t order by col2,col1) a) b group by b.col2,b.rn order by min(col1);
COL2 CN ---------- ---------- 2 3 9 2 2 2 3 1
SQL> select col2,count(col2) from( 2 select col1,col2,row_number() over(partition by col2 order by col1) rn from t 3 ) 4 group by col1+col2-rn,col2 5 order by min(col1);
from tab
group by col2
order by col1
from tab
group by col2
order by col1
這個肯定錯了,語法都不對
不要给我SELECT COL2,COUNT(1) FROM TAB GROUP BY COL2 ORDER BY COL1
select COL2,count(*)
from
(
select L.*,
ROW_NUMBER() OVER ( PARTITION BY COL2 ORDER BY COL1 DESC ) + COL1 AS GGM
FROM
(
SELECT *
from test_c t ORDER BY T.COL1
) L
) A
group by A.COL2,GGM
order by min(A.COL1)
from
(
select L.*,
ROW_NUMBER() OVER ( PARTITION BY COL2 ORDER BY COL1 DESC ) + COL1 AS GGM
FROM test_c L
) A
group by A.COL2,GGM
order by min(A.COL1)
create table tb (col1 int,col2 int);
insert into tb values(1,2);
insert into tb values(2,2);
insert into tb values(3,2);
insert into tb values(4,9);
insert into tb values(5,9);
insert into tb values(6,2);
insert into tb values(7,2);
insert into tb values(8,3);with t as
(select t1.col1,t1.col2,ROW_NUMBER()over(partition by col2 order by col1) as rn from tb t1)
select t.col2,COUNT(1) CNT
from t
group by t.col2,t.col1-rn
order by min(col1) COL2 CNT
1 2 3
2 9 2
3 2 2
4 3 1
2 select 1 col1, 2 col2 from dual union all
3 select 2 col1, 2 col2 from dual union all
4 select 3 col1, 2 col2 from dual union all
5 select 4 col1, 9 col2 from dual union all
6 select 5 col1, 9 col2 from dual union all
7 select 6 col1, 2 col2 from dual union all
8 select 7 col1, 2 col2 from dual union all
9 select 8 col1, 3 col2 from dual
10 )
11 select col2,
12 nvl(lead(col1) over(order by col1) - col1, 1) cnt --这里依赖于col1是连续的,若不连续则需用rownum替换col1
13 from (
14 select col1, col2,
15 lag(col1) over(partition by col2 order by col1) lag_col1
16 from tmp
17 )
18 where lag_col1 is null
19 or col1 - lag_col1 <> 1
20 order by col1
21 /
COL2 CNT
---------- ----------
2 3
9 2
2 2
3 1
(select 1 col1, 2 col2 from dual union all
select 2 col1, 2 col2 from dual union all
select 3 col1, 2 col2 from dual union all
select 4 col1, 9 col2 from dual union all
select 5 col1, 9 col2 from dual union all
select 6 col1, 2 col2 from dual union all
select 7 col1, 2 col2 from dual union all
select 8 col1, 3 col2 from dual)SELECT col2, COUNT(*) cnt
FROM (SELECT col1, col2, lag(col2, 1, -1) over(ORDER BY col1) pcol2 FROM tt) a
START WITH col2 <> pcol2
CONNECT BY PRIOR col1 = col1 - 1 AND
col2 = PRIOR col2
GROUP BY col2, col1 - LEVEL
ORDER BY col1 - LEVEL
SQL> select b.col2 col2,count(*) cn
2 from
3 (select a.col1,a.col2,a.col1+a.col2-rownum rn from
4 (select col1,col2 from t order by col2,col1) a) b group by b.col2,b.rn order by min(col1);
COL2 CN
---------- ----------
2 3
9 2
2 2
3 1
SQL> select col2,count(col2) from(
2 select col1,col2,row_number() over(partition by col2 order by col1) rn from t
3 )
4 group by col1+col2-rn,col2
5 order by min(col1);
COL2 COUNT(COL2)
---------- -----------
2 3
9 2
2 2
3 1
oracle QQ群:54775466
欢迎您的到来
大家一起探讨。