我用ms sql写的语句,先转到oracle里用
但是不太明白oracle的具体语法
帮我看下,谢谢mssql 语句如下:select * from person where userid='ad' and
case
when ttlflag=1 and DATEADD(DAY,accountttl,createtime)>GETDATE() then 1 --判定时间
when ttlflag=3 then 1
else 0
end =1;
但是不太明白oracle的具体语法
帮我看下,谢谢mssql 语句如下:select * from person where userid='ad' and
case
when ttlflag=1 and DATEADD(DAY,accountttl,createtime)>GETDATE() then 1 --判定时间
when ttlflag=3 then 1
else 0
end =1;
SELECT *
FROM PERSON
WHERE USERID = 'ad'
AND (CASE WHEN TTLFLAG = 1 AND CREATETIME + ACCOUNTTTL = SYSDATE
THEN 1
WHEN TTLFLAG = 3
THEN 3
ELSE 0
END) = 1;-- 根据我的想法,可简化之如下:
SELECT *
FROM PERSON
WHERE USERID = 'ad'
AND (TTLFLAG = 1
AND CREATETIME + ACCOUNTTTL = SYSDATE);
恩,第1开始我也是写的和你简化的一样
但是那样当TTLFLAG = 3时他出现的结果不对
SELECT *
FROM PERSON
WHERE USERID = 'ad'
AND ((TTLFLAG = 1
AND CREATETIME + ACCOUNTTTL = SYSDATE)
OR TTLFLAG = 3);
SELECT *
FROM PERSON
WHERE USERID = 'ad'
AND ((TTLFLAG = 1
AND CREATETIME + ACCOUNTTTL = SYSDATE)
OR TTLFLAG = 3);
-- TRY IT ..
SELECT *
FROM PERSON
WHERE USERID = 'ad'
AND (CASE WHEN TTLFLAG = 1 AND CREATETIME + ACCOUNTTTL = SYSDATE
THEN 1
WHEN TTLFLAG = 3
THEN 1
ELSE 0
END) = 1;-- 根据我的想法,可简化之如下:
SELECT *
FROM PERSON
WHERE USERID = 'ad'
AND (TTLFLAG = 1 OR TTLFLAG = 3
AND CREATETIME + ACCOUNTTTL = SYSDATE);-- 或者用DECODE也行。
case
when ttlflag=1 and accountttl+createtime>GETDATE() then 1 --判定时间
when ttlflag=3 then 1
else 0
end =1
case
when ttlflag=1 and accountttl+createtime>sysdate then 1 --判定时间
when ttlflag=3 then 1
else 0
end =1
(TTLFLAG = 1 OR TTLFLAG = 3
AND CREATETIME + ACCOUNTTTL = SYSDATE)这样的条件返回的数据和(CASE WHEN TTLFLAG = 1 AND CREATETIME + ACCOUNTTTL = SYSDATE
THEN 1
WHEN TTLFLAG = 3
THEN 1
ELSE 0
END) = 1还是有差距的吧。