sql - 如何克服以下查询中除以零的错误

Question

请在下面找到一个表结构

table A      Table B
10              10
20              10
30              0
40              0

我想划分A/B。如果有 30/0 我想将其显示为 0。

我想像这样输出

O/P
1
2
0
0

我们怎样才能做到这一点？

Table structure queries:
create table #test1(id int)

insert into #test1 values(10)
insert into #test1 values(20)
insert into #test1 values(30)
insert into #test1 values(40)

select top 100 * from #test1

create table #test2(id int)

insert into #test2 values(10)
insert into #test2 values(10)
insert into #test2 values(0)
insert into #test2 values(0)

select top 100 * from #test2

标签： sqlsql-serversql-server-2008

Answer 1

试试这个

SELECT A.id,B.id, IIF(B.id<>0,A.Id/B.ID,0) AS ExpectedResult,
       COALESCE( A.id/NULLIF(B.ID,0),0)  AS ExpectedResult2
FROM
(
SELECT Id,ROW_NUMBER()OVER(Order BY ID) As Seq FROM #test1
)As A
INNER JOIN 
(
SELECT Id,ROW_NUMBER()OVER(Order BY ID DESC) As Seq FROM #test2)AS B
ON A.Seq=B.Seq

结果

 id id  ExpectedResult  ExpectedResult2
10  10     1                    1
20  10     2                    2
30  0      0                    0
40  0      0                    0