把一个oracle查询语句改成SQL的

[Doson]

select CanSell,Signed,Records,AveragePrice,housetype,addtime from AC_SellExhilarant where (floor(sysdate - addtime) =1 or floor(sysdate - addtime) = 0) and DISTRICT = :DISTRICT order by decode(Housetype,'住宅类',0,'商业类',1,'办公类',2),AddTime DESC

请问各位高手这个怎么改

[shiyiwan]

select CanSell,Signed,Records,AveragePrice,housetype,addtime,
       case when Housetype = '住宅类' then 0
            when Housetype = '商业类' then 1
            when Housetype = '办公类' then 2
       end  rn
from   AC_SellExhilarant
where (floor(sysdate - addtime) = 1 or floor(sysdate - addtime) = 0)
and    DISTRICT = :DISTRICT
order  by rn, AddTime DESC;

floor向下取整这个函数标准sql中不确定是否能用或者有相似的函数

[shiyiwan]

查了下，floor是标准sql数学函数，可以使用，但是获取当前时间，sqlserver,mysql,oracle，db2,informix都有各自不同的函数。

select CanSell,Signed,Records,AveragePrice,housetype,addtime,
from   AC_SellExhilarant
where (floor(sysdate - addtime) = 1 or floor(sysdate - addtime) = 0)
and    DISTRICT = :DISTRICT
order  by case when Housetype = '住宅类' then 0
               when Housetype = '商业类' then 1
               when Housetype = '办公类' then 2
          end  rn, AddTime DESC;

[Doson]

[Quote=引用 1 楼 shiyiwan 的回复:]
SQL codeselect CanSell,Signed,Records,AveragePrice,housetype,addtime,casewhen Housetype='住宅类'then0when Housetype='商业类'then1when Housetype='办公类'then2end rnfrom AC_SellExhilarantwhere (floor(sysdate- addtime)=1orfloor(sysdate- addtime)=0)and DISTRICT= :DISTRICTorderby rn, AddTimeDESC;

floor向下取整这个函数标准sql中不确定是否能用或者有相似的函数
[/Quote]

能不能把 case 到 from 之间的语句放到where的后面？？

[shiyiwan]

可以的，去掉end后面那个别名rn吧

[Doson]

非常感谢shiyiwan

[shiyiwan]

不客气