SELECT ID ,
NAME,
DATE,
UPDATE
FROM STUDENT WHERE ( CASE
WHERE DATE IS NULL THEN
UPDATE BETWEEN '2008/08/01' AND '2008/08/02'
ELSE
UPDATE BETWEEN '2008/07/01 AND '2008/07/31'
END
)
WHERE里面的DATE是上面SELECT出来的,不知道这样写对不对我的需求是根据DATE是否为空,来选择WHERE条件,大家有什么好的办法吗
NAME,
DATE,
UPDATE
FROM STUDENT WHERE ( CASE
WHERE DATE IS NULL THEN
UPDATE BETWEEN '2008/08/01' AND '2008/08/02'
ELSE
UPDATE BETWEEN '2008/07/01 AND '2008/07/31'
END
)
WHERE里面的DATE是上面SELECT出来的,不知道这样写对不对我的需求是根据DATE是否为空,来选择WHERE条件,大家有什么好的办法吗
1.date,update是关键字不能做字段
2.case when then when then end是这样用的
改后再贴
关键字,我承认我弄错了,因为是在发帖的时候,自己编的字段,没想那么多case when then else end 也是可以的,我都做过实验了当然,你说的 case when then when then end 也是可以用的
SELECT ID ,
NAME,
ENT_DATE,
UPD_DATE
FROM STUDENT WHERE ( CASE
WHEN DATE IS NULL
THEN UPD_DATE BETWEEN '2008/08/01' AND '2008/08/02'
ELSE
THEN UPD_DATE BETWEEN '2008/07/01' AND '2008/07/31'
END
)
NAME,
ENT_DATE,
UPD_DATE
FROM STUDENT WHERE ( CASE
WHEN ENT_DATE IS NULL THEN
UPD_DATE BETWEEN '2008/08/01' AND '2008/08/02'
ELSE
UPD_DATE BETWEEN '2008/07/01' AND '2008/07/31'
END
)我试了没通过SELECT ID ,
NAME,
ENT_DATE,
UPD_DATE
FROM STUDENT WHERE ( ENT_DATE IS NULL AND UPD_DATE BETWEEN '2008/08/01' AND '2008/08/02')
OR ( ENT_DATE IS NOT NULL AND UPD_DATE BETWEEN '2008/07/01' AND '2008/07/31')
这样可以
这么用:
要这么写才行(麻烦的很):
例子:
SELECT *
FROM SCOTT.EMP S
WHERE S.HIREDATE BETWEEN
(CASE WHEN S.EMPNO > 8000 THEN TO_DATE('2008/08/01', 'yyyy/mm/dd') ELSE
TO_DATE('2008/07/01', 'yyyy/mm/dd') END) AND
(CASE WHEN S.EMPNO > 8000 THEN TO_DATE('2008/08/02', 'yyyy/mm/dd') ELSE
TO_DATE('2008/07/31', 'yyyy/mm/dd') END);
drop table a;
create table a(id number,name varchar2(10),ent_date date,upd_date date);
insert into a values(1,'a',null,to_date('2008/08/01','yyyy-mm-dd'));
insert into a values(2,'aa',null,to_date('2008/08/02','yyyy-mm-dd'));
insert into a values(3,'aaa',to_date('2008/01/01','yyyy-mm-dd'),to_date('2008/07/01','yyyy-mm-dd'));
insert into a values(4,'aaaa',to_date('2008/01/01','yyyy-mm-dd'),to_date('2008/07/31','yyyy-mm-dd'));
insert into a values(5,'aaaaa',to_date('2008/01/01','yyyy-mm-dd'),to_date('2008/06/30','yyyy-mm-dd'));
insert into a values(6,'aaaaaa',null,to_date('2008/08/08','yyyy-mm-dd'));
commit;select id, name, ent_date, upd_date
from a
where (ent_date is null and
(upd_date between to_date('2008/08/01', 'yyyy-mm-dd') and
to_date('2008/08/02', 'yyyy-mm-dd')))
or (ent_date is not null and
(upd_date between to_date('2008/07/01', 'yyyy-mm-dd') and
to_date('2008/07/31', 'yyyy-mm-dd')))