select * from tab where (column1 = 1 and column2 between 0 and 10 ) or (column3 between 10 and 100 ) select * from tab where case when column1 = 1 and column2 between 0 and 10 then 1 when column1 != 1 and column3 between 10 and 100 then 1 else 0 end = 1 )
select * from tab where (type='出售' and price>=0 and price <10) or (type='定价' and price>=10 and price<100) or type not in ('出售','定价')
case when 条件1 then 结果1 when 条件2 then 结果2 else 结果3 注意点: 1、以CASE开头,以END结尾 2、分支中WHEN 后跟条件,THEN为显示结果 3、ELSE 为除此之外的默认情况,类似于高级语言程序中switch case的default,可以不加 4、END 后跟别名
用decode(),去查一下.比CASE好用多了 也简洁多了
select * from tab where (case when column1 = 1 and column2 between 0 and 10 then 1 when column1 != 1 and column3 between 10 and 100 then 1 else 0 end = 1);
where (column1 = 1 and column2 between 0 and 10 ) or (column3 between 10 and 100 )
select * from tab
where case when column1 = 1 and column2 between 0 and 10 then 1 when column1 != 1 and column3 between 10 and 100 then 1 else 0 end = 1 )
当type = 出售 时显示出 0 到10元 水果名
当type = 定价 时显示出 10 到 100 元 的水果名
否则 显示所有水果名
(type='出售' and price>=0 and price <10)
or
(type='定价' and price>=10 and price<100)
or
type not in ('出售','定价')
注意点:
1、以CASE开头,以END结尾
2、分支中WHEN 后跟条件,THEN为显示结果
3、ELSE 为除此之外的默认情况,类似于高级语言程序中switch case的default,可以不加
4、END 后跟别名
select *
from tab
where (case when column1 = 1 and column2 between 0 and 10 then 1 when
column1 != 1 and column3 between 10 and 100 then 1 else 0 end = 1);