不用存储过程只用SELECT如何实现？？？

[sense]

我有一个表数据：
COL1 COL2
1 2
2 2
3 2
4 9
5 9
6 2
7 2
8 3
...

想按COL1排序得到COL2分组后的数据和行数（顺序也必须按原来的）：
COL2 行数
2 3
9 2
2 2
3 1
...

[iqlife]

select col2,count(col2)
from tab
group by col2
order by col1

[ngx20080110]

select col2,count(col2)
from tab
group by col2
order by col1
這個肯定錯了，語法都不對

[sense]

这个语句就不要拿出来了，认真看我要的结果！！

你那语句不行，你会把COL2是2的记录全部分一个组！！！

[sense]

我补充一下：

注意：COL2是2的记录根据顺序不一定是在一个组的。
不要给我SELECT COL2,COUNT(1) FROM TAB GROUP BY COL2 ORDER BY COL1

[codearts]

先顶一下

[ngx20080110]

期待高手解答

[永生天地]

如果col1不是连续，就要用rownum，明天研究一下

[木子0204]

用group by

[kinlin]

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)

[ngx20080110]

[Quote=引用 9 楼 kinlin 的回复:]
SQL code

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
gro……
[/Quote]
厲害！

[kinlin]

呵呵，不好意思，写麻烦了

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

[codearts]

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

[mantisXF]

连续数问题。

[tangren]

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

[dingjun123]

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

[dingjun123]

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

[liqzhangq]

顶一下
oracle QQ群：54775466
欢迎您的到来
大家一起探讨。

[sense]

来者有分