这样的一批数据 主要用到人员ID、日期,用存储过程。目前大概逻辑是,如果这个人员的最大日期不是当前系统时间的当月最后一天,走路先A。如果等于走路先B下面答题逻辑代码
for rec in (select id ,max(date) from table group by id)
loopif
路线A
else
路先B
end loop;-------但问了小头头,说不要用IF,含糊其辞大概意思是if性能不好,叫我用decode......求高性能的解决方案
for rec in (select id ,max(date) from table group by id)
loopif
路线A
else
路先B
end loop;-------但问了小头头,说不要用IF,含糊其辞大概意思是if性能不好,叫我用decode......求高性能的解决方案
decode(trunc(max(date))=trunc(last_day(sysdate)) then '走路线A','走路线B')
from table_name
group by id;
decode(trunc(max(date))=trunc(last_day(sysdate)) then '走路线B','走路线A')
from table_name
group by id;
INSERT INTO T(ID,CDATE) VALUES(1,TO_DATE('2010-01-11'));
INSERT INTO T(ID,CDATE) VALUES(1,TO_DATE('2010-01-14'));
INSERT INTO T(ID,CDATE) VALUES(1,TO_DATE('2010-01-19'));
INSERT INTO T(ID,CDATE) VALUES(2,TO_DATE('2010-01-08'));
INSERT INTO T(ID,CDATE) VALUES(2,TO_DATE('2010-01-17'));
INSERT INTO T(ID,CDATE) VALUES(2,TO_DATE('2010-01-31'));
INSERT INTO T(ID,CDATE) VALUES(2,TO_DATE('2011-01-31'));select id ,
trunc(max(cdate)) as cdate,
decode(trunc(max(cdate)),trunc(last_day(sysdate)),'走路线B','走路线A') as lines
from t
group by id;
select id,decode(sign(trunc(last_day(sysdate))-trunc(max(date))),0,'路先B','路先a')
from tb group by id不晓得你那路线是做什么
insert into yourtable
select *
from othertable
where adate = decode(trunc(adate),
last_day(sysdate),
last_day(sysdate + 1),
last_day);
INSERT INTO T(ID,CDATE) VALUES(1,TO_DATE('2010-01-11'));
INSERT INTO T(ID,CDATE) VALUES(1,TO_DATE('2010-01-14'));
INSERT INTO T(ID,CDATE) VALUES(1,TO_DATE('2010-01-19'));
INSERT INTO T(ID,CDATE) VALUES(2,TO_DATE('2010-01-08'));
INSERT INTO T(ID,CDATE) VALUES(2,TO_DATE('2010-01-17'));
INSERT INTO T(ID,CDATE) VALUES(2,TO_DATE('2010-01-31'));
INSERT INTO T(ID,CDATE) VALUES(2,TO_DATE('2011-01-31'));select id ,
trunc(max(cdate)) as cdate,
decode(trunc(max(cdate)),trunc(last_day(sysdate)),'走路线B','走路线A') as lines
from t
group by id;declare
v_id t.id%type;
v_max_cdate date;
v_curr_max_day date; -- 本月最后一天
v_next_month_max_day date; -- 下月最后一天
cursor cur is select id, trunc(max(cdate)) as max_cdate from t group by id;
begin
select trunc(last_day(sysdate)), -- 需要截断时、分秒的话,用trunc()函数
trunc(last_day(add_months(sysdate,1)))
into v_curr_max_day, v_next_month_max_day
from dual;
for i in cur loop
v_id := i.id;
v_max_cdate := i.max_cdate;
if v_max_cdate = v_curr_max_day then
insert into t(id,cdate) values(v_id,v_next_month_max_day);
else
insert into t(id,cdate) values(v_id,v_curr_max_day);
end if;
end loop;
commit;
end;
/
CREATE TABLE T(ID NUMBER(18,0), CDATE DATE);
INSERT INTO T(ID,CDATE) VALUES(1,TO_DATE('2010-01-11'));
INSERT INTO T(ID,CDATE) VALUES(1,TO_DATE('2010-01-14'));
INSERT INTO T(ID,CDATE) VALUES(1,TO_DATE('2010-01-19'));
INSERT INTO T(ID,CDATE) VALUES(2,TO_DATE('2010-01-08'));
INSERT INTO T(ID,CDATE) VALUES(2,TO_DATE('2010-01-17'));
INSERT INTO T(ID,CDATE) VALUES(2,TO_DATE('2010-01-31'));
INSERT INTO T(ID,CDATE) VALUES(2,TO_DATE('2011-01-31'));commit;insert into t
select id ,
decode(trunc(max(cdate)),trunc(last_day(sysdate)),trunc(last_day(add_months(sysdate,1))),trunc(last_day(sysdate))) as lines
from t
group by id;
有现成的函数不用,却用基础sql把功能累出来,你觉得效率会高吗?