请教一个 SQL 的问题，十分感谢！

[blackkettle]

各位神侠，请教一个 SQL 的问题。

现有一个表，名字为 table1, 表里有两列，一列是序号ID，一列是数量 NUM1（只取 0，1 两个值），比如

ID1 NUM1
1 0
2 0
3 0
4 1
5 1
6 0
7 0
8 0
9 1
...

要求在这个表上加一列 NUM2，表示NUM1中对应的序数，

对上表来说，就是

ID1 NUM1 NUM2
1 0 1
2 0 1
3 0 1
4 1 2
5 1 2
6 0 3
7 0 3
8 0 3
9 1 4
...


请问如何实现？

万分感谢！

[blackkettle]

有呢，大侠。

这个是要在一个循环里进行的操作...

[zujinsheng]

MARK 学习.. 开窗函数行不能行不？

[oO寒枫Oo]

declare
  cursor my_cur is
    select  t.rowid row_id,t.ID1,t.NUM1
    from table1 t
    order by 1;
  v_count number;
  v_j  number;
  v_pid number;
begin
  v_count:=0;
  v_j:=1;
  v_pid:=0;
  for row_t in my_cur loop
    if (v_pid=row_t.NUM1) then 
	begin
       update table1 set NUM2=v_j
         where rowid=row_t.row_id; 
    end;
	  else 
       begin
          v_j:=v_j+1;
		  update table1 set NUM2=v_j
           where rowid=row_t.row_id;
		   v_pid:=row_t.NUM1;
       end;
	 end if;  
    v_count:=v_count+1;
    if (v_count>=2000) then
      commit;
      v_count:=0;
    end if;
  end loop;
  commit;
end;
/

不知道效率如何。

[princes1]

select a.*,dense_rank() over(order by NUM1 ) as num2 from table1 a

[blackkettle]

我自己搞的一个，

大侠批评一下吧。

create table test(id1 int, num1 int);

insert into test values(1,0);
insert into test values(2,0);
insert into test values(3,0);
insert into test values(4,1);
insert into test values(5,1);
insert into test values(6,0);
insert into test values(7,0);
insert into test values(8,1);
insert into test values(9,1);
insert into test values(10,1);
insert into test values(11,0);
insert into test values(12,0);

select a.id1, a.num1, sum(abs(num2)) over(order by id1) num2
from (SELECT id1, num1, num1 - lag(num1, 1, 0) over(order by id1) as num2
from test) a



ID1 NUM1 NUM2
1 0 0
2 0 0
3 0 0
4 1 1
5 1 1
6 0 2
7 0 2
8 1 3
9 1 3
10 1 3
11 0 4
12 0 4

[Brayden1988]

cursor myCur is select * from test;
oneRow test%rowtype;
flag1 number;
flag2 number;
flag3 number;
begin
flag1:=0;
flag2:=-1;
flag3:=0;
open myCur;
fetch myCur into oneRow;
while(myCur%found)
loop
if flag2=-1 then
select num1 into flag2 from test where id=oneRow.id;
flag1:=flag1+1;
UPDATE test set num2=flag1 where id=oneRow.id;
else
select num1 into flag3 from test where id=oneRow.id;
if flag3<>flag2 then
flag1:=flag1+1;
end if;
flag2:=flag3;
UPDATE test set num2=flag1 where id=oneRow.id;
end if;
fetch myCur into oneRow;
end loop;
close myCur;

[blackkettle]

[Quote=引用 7 楼 princes1 的回复:]

select a.*,dense_rank() over(order by NUM1 ) as num2 from table1 a
[/Quote]


这个好像把 ID1的顺序打乱了吧...

[blackkettle]

我自己写了一个，

请大侠批评指正一下。

create table test(id1 int, num1 int);

insert into test values(1,0);
insert into test values(2,0);
insert into test values(3,0);
insert into test values(4,1);
insert into test values(5,1);
insert into test values(6,0);
insert into test values(7,0);
insert into test values(8,1);
insert into test values(9,1);
insert into test values(10,1);
insert into test values(11,0);
insert into test values(12,0);


select a.id1, a.num1, sum(abs(num2)) over(order by id1) num2
from (SELECT id1, num1, num1 - lag(num1, 1, 0) over(order by id1) as num2
from test) a


--
ID1 NUM1 NUM2
1 0 0
2 0 0
3 0 0
4 1 1
5 1 1
6 0 2
7 0 2
8 1 3
9 1 3
10 1 3
11 0 4
12 0 4

[YeHuai1991]

使用开窗函数中的dense_rank()
SQL语句如下：select ID1,NUM1,dense_rank() OVER ( order by NUM1 DESC) as NUM2

from table1

[princes1]

[Quote=引用 10 楼 blackkettle 的回复:]
引用 7 楼 princes1 的回复:

select a.*,dense_rank() over(order by NUM1 ) as num2 from table1 a



这个好像把 ID1的顺序打乱了吧...
[/Quote]

如果序列也也排序的话加上一个排序不就可以了
select a.*,dense_rank() over(order by id1,NUM1 ) as num2 from TEST a

[strive_bo]

  SELECT T.ID, T.NUM, DENSE_RANK() OVER(ORDER BY T.NUM ASC)  FROM TABLE1 T

可以先用create table as SELECT T.ID, T.NUM, DENSE_RANK() OVER(ORDER BY T.NUM ASC) FROM TABLE1 T 然后你就可以轻松的得到结果，也不用存储过程

[blackkettle]

[Quote=引用 13 楼 princes1 的回复:]

引用 10 楼 blackkettle 的回复:
引用 7 楼 princes1 的回复:

select a.*,dense_rank() over(order by NUM1 ) as num2 from table1 a



这个好像把 ID1的顺序打乱了吧...


如果序列也也排序的话加上一个排序不就可以了
select a.*,dense_rank() ove……
[/Quote]


select a.*,dense_rank() over(order by id1,NUM1 ) as num2 from TEST a

ID1 NUM1 NUM2
1 0 1
2 0 2
3 0 3
4 1 4
5 1 5
6 0 6
7 0 7
8 1 8
9 1 9
10 1 10
11 0 11
12 0 12


好像不行吧

[princes1]

是不行 没看清你的题目 不好意思

[blackkettle]

[Quote=引用 19 楼 xiaobn_cn 的回复:]

引用 8 楼 blackkettle 的回复:
我自己搞的一个，

大侠批评一下吧。

create table test(id1 int, num1 int);

insert into test values(1,0);
insert into test values(2,0);
insert into test values(3,0);
insert into test……

SQL code

select a.id1, a.num1, sum(abs(num2)) over(order by id1) num2 from (SELECT id1, num1, case lag(num1, 1, null) over(order by id1) when num1 then 0 else 1 end as num2 from test) a


[/Quote]


这个非常好！多谢！

[xunmengzhi]

OVER BY

[xiaobn_cn]

[Quote=引用 8 楼 blackkettle 的回复:]
我自己搞的一个，

大侠批评一下吧。

create table test(id1 int, num1 int);

insert into test values(1,0);
insert into test values(2,0);
insert into test values(3,0);
insert into test values(4,1);
insert into……
[/Quote]

楼主的思路是正确的，不过你的SQL有点小BUG，就是当第1行为0时你的排序是从0开始的，当第1行为1时你的排序是从1开始。将你的SQL做个小修改就可以实现任何情况下都是从1开始排序了。

select a.id1, a.num1, sum(abs(num2)) over(order by id1) num2
  from (SELECT id1, num1, case lag(num1, 1, null) over(order by id1) when num1 then 0 else 1 end as num2
  from test) a

[dzntree]

[Quote=引用 8 楼 blackkettle 的回复:]

我自己搞的一个，

大侠批评一下吧。

create table test(id1 int, num1 int);

insert into test values(1,0);
insert into test values(2,0);
insert into test values(3,0);
insert into test values(4,1);
insert int……
[/Quote]
你自己的就挺好啊，只要把lag(num1, 1, 0)改成lag(num1, 1, 1)就行了

[xiaobn_cn]

[Quote=引用 20 楼 dzntree 的回复:]
你自己的就挺好啊，只要把lag(num1, 1, 0)改成lag(num1, 1, 1)就行了
[/Quote]

这两个没有区别的好吧？

[dzntree]

[Quote=引用 21 楼 xiaobn_cn 的回复:]

引用 20 楼 dzntree 的回复:
你自己的就挺好啊，只要把lag(num1, 1, 0)改成lag(num1, 1, 1)就行了


这两个没有区别的好吧？
[/Quote]
仅仅是改一下default值，使得结果从1开始

[xiaobn_cn]

[Quote=引用 22 楼 dzntree 的回复:]
引用 21 楼 xiaobn_cn 的回复:

引用 20 楼 dzntree 的回复:
你自己的就挺好啊，只要把lag(num1, 1, 0)改成lag(num1, 1, 1)就行了


这两个没有区别的好吧？

仅仅是改一下default值，使得结果从1开始
[/Quote]

你这个改法不对，如果测试数据的第1条记录的num1是1的话，你这个又变成从0开始了。