update T set times = (select count( * )
from (
select * from T
where date < (
select date
from T
where rownum = i;
)
)
)
where rownum = i;
这样可以吗,有个变量i是你要写的row. 应该在一个loop 里完成.
from (
select * from T
where date < (
select date
from T
where rownum = i;
)
)
)
where rownum = i;
这样可以吗,有个变量i是你要写的row. 应该在一个loop 里完成.
SQL> alter session set nls_date_format = 'yyyy.mm.dd';Session altered.SQL>
SQL> insert into t values('A','2003.01.01',null);1 row created.SQL> insert into t values('B','2003.01.02',null);1 row created.SQL> insert into t values('A','2003.01.03',null);1 row created.SQL> insert into t values('A','2003.01.04',null);1 row created.SQL> insert into t values('C','2003.01.05',null);1 row created.SQL> insert into t values('C','2003.01.06',null);1 row created.SQL> insert into t values('A','2003.01.07',null);1 row created.SQL> insert into t values('B','2003.01.08',null);1 row created.SQL>
SQL> select name,dt,row_number() over (partition by name order by dt) times
2 from t
3 order by dt;N DT TIMES
- ---------- ----------
A 2003.01.01 1
B 2003.01.02 1
A 2003.01.03 2
A 2003.01.04 3
C 2003.01.05 1
C 2003.01.06 2
A 2003.01.07 4
B 2003.01.08 28 rows selected.SQL>
SQL> update t t1 set t1.times =
2 (select count(1) from t t2
3 where t1.dt >= t2.dt
4 and t1.name = t2.name);8 rows updated.SQL>
SQL> select * from t;N DT TIMES
- ---------- ----------
A 2003.01.01 1
B 2003.01.02 1
A 2003.01.03 2
A 2003.01.04 3
C 2003.01.05 1
C 2003.01.06 2
A 2003.01.07 4
B 2003.01.08 28 rows selected.SQL>
SQL> drop table t;Table dropped.SQL>
where date < (
select date
from T
where rownum = i;
) and name = (
select name
from T
where rownum = i;
)
数据 a b c A 1 1
B 2 1
A 3 2
C 4 1
B 5 2sql 语法
update dd
set c=(select count(*)
from dd t
where t.b<=dd.b
and t.a=dd.a)
(select count(1) from t t2
where t1.dt >= t2.dt
and t1.name = t2.name);有点问题,谢谢.!