"在取得的记录中判断是否有start_date 〉系统时间的记录"
====================================================
什么意思?是start_date 有可能为空值吗?
如果是空值就不要,不是空值就要?是这样啊?
累~
====================================================
什么意思?是start_date 有可能为空值吗?
如果是空值就不要,不是空值就要?是这样啊?
累~
调试欢乐多
--- -- ---------- -------- --------
003 01 1 20040101 20040909
003 01 1 20040109 20041209
004 01 1 20040101 20041101
005 02 1 20040201 20041101SQL> select count(*) from (select ut_id,start_date,tky_dte_id from
2 (select ut_id,start_date,tky_dte_id,(rank() over (partition by ut_id order
by ut_id,tky_dte_id desc)) rid from table1 where hn_id=&hn_id and srt_id=&srt_id
)
3 where rid=1) where start_date=to_char(sysdate,'yyyymmdd');
Enter value for hn_id: 01
Enter value for srt_id: 1
old 2: (select ut_id,start_date,tky_dte_id,(rank() over (partition by ut_id or
der by ut_id,tky_dte_id desc)) rid from table1 where hn_id=&hn_id and srt_id=&sr
t_id)
new 2: (select ut_id,start_date,tky_dte_id,(rank() over (partition by ut_id or
der by ut_id,tky_dte_id desc)) rid from table1 where hn_id=01 and srt_id=1) COUNT(*)
----------
0SQL> update table1 set start_date=start_date+1;4 rows updated.SQL> select * from table1;UT_ HN SRT_ID TKY_DTE_ START_DA
--- -- ---------- -------- --------
003 01 1 20040101 20040910
003 01 1 20040109 20041210
004 01 1 20040101 20041102
005 02 1 20040201 20041102SQL> select count(*) from (select ut_id,start_date,tky_dte_id from
2 (select ut_id,start_date,tky_dte_id,(rank() over (partition by ut_id order
by ut_id,tky_dte_id desc)) rid from table1 where hn_id=&hn_id and srt_id=&srt_id
)
3 where rid=1) where start_date=to_char(sysdate,'yyyymmdd');
Enter value for hn_id: 01
Enter value for srt_id: 1
old 2: (select ut_id,start_date,tky_dte_id,(rank() over (partition by ut_id or
der by ut_id,tky_dte_id desc)) rid from table1 where hn_id=&hn_id and srt_id=&sr
t_id)
new 2: (select ut_id,start_date,tky_dte_id,(rank() over (partition by ut_id or
der by ut_id,tky_dte_id desc)) rid from table1 where hn_id=01 and srt_id=1) COUNT(*)
----------
1
(select ut_id,start_date,tky_dte_id,rank() over (partition by ut_id order
by ut_id,tky_dte_id desc) rid from table1 where
start_date=to_char(sysdate,'yyyymmdd') and hn_id=&hn_id and srt_id=&srt_id
)
where rid=1);