我有一个tb表
id num1 num2
1 3.00 2
2 4.00 0
3 5.00 2.50
4 6.00 3.00
5 7.00 0现在怎样通过SQL语句查询得到以下结果:
id num1 num2 ratio
1 3.00 2 1.50
2 4.00 0 0
3 5.00 2.50 2.00
4 6.00 3.00 2.00
5 7.00 0 0
条件是:如果num2=0,则ratio=0
如果num2<>0,则ratio=num1/num2谢谢!!!
id num1 num2
1 3.00 2
2 4.00 0
3 5.00 2.50
4 6.00 3.00
5 7.00 0现在怎样通过SQL语句查询得到以下结果:
id num1 num2 ratio
1 3.00 2 1.50
2 4.00 0 0
3 5.00 2.50 2.00
4 6.00 3.00 2.00
5 7.00 0 0
条件是:如果num2=0,则ratio=0
如果num2<>0,则ratio=num1/num2谢谢!!!
-------------------------------------------> 测试时间:2009-07-16
--> 我的淘宝:http://shop36766744.taobao.com/--------------------------------------------------if object_id('[tb]') is not null drop table [tb]
create table [tb]([id] int,[num1] numeric(3,2),[num2] numeric(3,2))
insert [tb]
select 1,3.00,2 union all
select 2,4.00,0 union all
select 3,5.00,2.50 union all
select 4,6.00,3.00 union all
select 5,7.00,0select *,ratio=case when num2=0 then 0 else num1/num2 end
from tb/*
id num1 num2 ratio
----------- ----- ----- -----------
1 3.00 2.00 1.500000
2 4.00 .00 .000000
3 5.00 2.50 2.000000
4 6.00 3.00 2.000000
5 7.00 .00 .000000(所影响的行数为 5 行)
*/
drop table TB
insert @tb select 2 , 4.00 , 0
insert @tb select 3 , 5.00 , 2.50
insert @tb select 4 , 6.00 , 3.00
insert @tb select 5 , 7.00 , 0
select * ,ratio=case when num2=0 then 0 else cast(num1/num2 as decimal(9,2)) end from @tb/*
id num1 num2 ratio
----------- ----------- ----------- -----------
1 3.00 2.00 1.50
2 4.00 .00 .00
3 5.00 2.50 2.00
4 6.00 3.00 2.00
5 7.00 .00 .00(所影响的行数为 5 行)
*/
t.*,case when num2=0 then 0 else num1/num2 end as ratio
from
tb t
-->测试数据@tb
DECLARE @tb TABLE(id INT, num1 FLOAT, num2 FLOAT)
INSERT INTO @tb
SELECT 1, 3.00, 2 UNION ALL
SELECT 2, 4.00, 0 UNION ALL
SELECT 3, 5.00, 2.50 UNION ALL
SELECT 4, 6.00, 3.00 UNION ALL
SELECT 5, 7.OO, 0-->SQL查询
SELECT *, ratio=CASE WHEN num2 = 0 then 0
ELSE num1/num2
END
FROM @tb