select min_lvl, max_lvl
, aa=case when min_lvl is not null and max_lvl is not null then rtrim(min_lvl*1.0/max_lvl)
else '' end
from tb
, aa=case when min_lvl is not null and max_lvl is not null then rtrim(min_lvl*1.0/max_lvl)
else '' end
from tb
insert into tb values(5 , 8 )
insert into tb values(6 , 4 )
insert into tb values(null, null)
insert into tb values(0 , 0 )
goselect top 1 min_lvl , max_lvl , aa = cast(min_lvl*1.0/max_lvl as decimal(18,3)) from tb where (min_lvl <> 0 and min_lvl is not null) and (max_lvl <> 0 and max_lvl is not null)
union all
select distinct min_lvl , max_lvl , 0 from tb where (min_lvl = 0 and max_lvl = 0 ) or (min_lvl is null and max_lvl is null)drop table tb/*
min_lvl max_lvl aa
----------- ----------- --------------------
5 8 .625
NULL NULL .000
0 0 .000(所影响的行数为 3 行)
*/
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
create table [tb]([min_lvl] int,[max_lvl] int)
insert [tb]
select 5,8 union all
select 6,4 union all
select null,null union all
select 0,0select min_lvl, max_lvl
, aa=case when min_lvl is not null and max_lvl>0 then rtrim(cast(min_lvl*1.0/max_lvl as decimal(10,3)))
when max_lvl=0 then '0'
else '' end
from tb
where min_lvl<=max_lvl or max_lvl is null
/*
min_lvl max_lvl aa
----------- ----------- -----------------------------------------
5 8 0.625
NULL NULL
0 0 0(3 行受影响)
*/drop table [tb]
insert into tb values(5 , 8 )
insert into tb values(6 , 4 )
insert into tb values(null, null)
insert into tb values(0 , 0 )
goselect top 1 min_lvl , max_lvl , aa = cast(cast(min_lvl*1.0/max_lvl as decimal(18,3)) as varchar) from tb where (min_lvl <> 0 and min_lvl is not null) and (max_lvl <> 0 and max_lvl is not null)
union all
select distinct min_lvl , max_lvl , '' from tb where (min_lvl is null and max_lvl is null)
union all
select distinct min_lvl , max_lvl , '0' from tb where (min_lvl = 0 and max_lvl = 0 ) drop table tb/*
min_lvl max_lvl aa
----------- ----------- ------------------------------
5 8 0.625
NULL NULL
0 0 0(所影响的行数为 3 行)
*/
insert into tb values(5 , 8 )
insert into tb values(6 , 4 )
insert into tb values(null, null)
insert into tb values(0 , 0 )
goselect top 1 min_lvl , max_lvl , aa = cast(cast(min_lvl*1.0/max_lvl as decimal(18,3)) as varchar) from tb where (min_lvl <> 0 and min_lvl is not null) and (max_lvl <> 0 and max_lvl is not null)
union all
select distinct min_lvl , max_lvl , '' from tb where (min_lvl is null and max_lvl is null)
union all
select distinct min_lvl , max_lvl , '0' from tb where (min_lvl = 0 and max_lvl = 0 )
/*
min_lvl max_lvl aa
----------- ----------- ------------------------------
5 8 0.625
NULL NULL
0 0 0(所影响的行数为 3 行)
*/
select min_lvl , max_lvl , aa = cast(cast(min_lvl*1.0/max_lvl as decimal(18,3)) as varchar) from tb where min_lvl < max_lvl and (min_lvl <> 0 and min_lvl is not null) and (max_lvl <> 0 and max_lvl is not null)
union all
select distinct min_lvl , max_lvl , '' from tb where (min_lvl is null and max_lvl is null)
union all
select distinct min_lvl , max_lvl , '0' from tb where (min_lvl = 0 and max_lvl = 0 )
/*
min_lvl max_lvl aa
----------- ----------- ------------------------------
5 8 0.625
NULL NULL
0 0 0(所影响的行数为 3 行)
*/
drop table tb