例如想写这样的sql
select id,name,datea,dateb from tb where (if name=3 then datea>某日 and datea<某日 else dateb>某日 and dateb<某日)
如何写呢?
select id,name,datea,dateb from tb where (if name=3 then datea>某日 and datea<某日 else dateb>某日 and dateb<某日)
如何写呢?
--case也可以
select id,name,datea,dateb
from tb
where case when name=3 then datea>某日 and datea<某日
else dateb>某日 and dateb<某日 end
FROM tb
WHERE NAME = 3 AND
datea > 某日 AND
datea < 某日 OR
NAME != 3 AND
dateb > 某日 AND
dateb < 某日
--也可以这样case 分解为or
select id,name,datea,dateb
from tb
where (name=3 then datea>某日 and datea<某日) or
(name<>3dateb>某日 and dateb<某日)
--键盘大块了 少了个and
select id,name,datea,dateb
from tb
where (name=3 then datea>某日 and datea<某日) or
(name<>3 and dateb>某日 and dateb<某日)
SELECT ID,
NAME,
DATEA,
DATEB
FROM TB
WHERE CASE
WHEN NAME = 3 THEN
(DATEA > SYSDATE - 7 AND DATEB < SYSDATE)
ELSE
(DATEA > SYSDATE - 3 AND DATEB < SYSDATE)
END 转化成and 和or :
WITH TB AS
(SELECT 1 AS ID, 1 AS NAME, SYSDATE - 2 AS DATEA, SYSDATE AS DATEB FROM DUAL UNION
SELECT 2 AS ID, 2 AS NAME, SYSDATE - 4 AS DATEA, SYSDATE AS DATEB FROM DUAL UNION
SELECT 3 AS ID, 3 AS NAME, SYSDATE - 6 AS DATEA, SYSDATE AS DATEB FROM DUAL UNION
SELECT 4 AS ID, 3 AS NAME, SYSDATE - 8 AS DATEA, SYSDATE AS DATEB FROM DUAL)
SELECT ID,
NAME,
DATEA,
DATEB
FROM TB
WHERE (NAME = 3 AND DATEA > SYSDATE - 7 AND DATEB < SYSDATE+1)
OR (DATEA > SYSDATE - 3 AND DATEB < SYSDATE+1)ID NAME DATEA DATEB
1 1 2010/5/19 14:50:15 2010/5/21 14:50:15
3 3 2010/5/15 14:50:15 2010/5/21 14:50:15
。