CREATE VIEW dbo.MAV_ly
AS
SELECT
A.类别,
A.a1*1000.0/B.a1 AS [08年1月],
A.a2*1000.0/B.a2 AS [08年2月],
A.a3*1000.0/B.a3 AS [08年3月],
A.a4*1000.0/B.a4 AS [08年4月],
A.a5*1000.0/B.a5 AS [08年5月],
A.a6*1000.0/B.a6 AS [08年6月]
FROM ly_mount AS A
JOIN ly_shix AS B
ON A.类别=B.类别
上述程序中,其中分母有为0的情况,SQL SERVER报被零除错误,如何避免这个情况>????如果分母为0,最好是计算结果为0或是NULL就可以了。
AS
SELECT
A.类别,
A.a1*1000.0/B.a1 AS [08年1月],
A.a2*1000.0/B.a2 AS [08年2月],
A.a3*1000.0/B.a3 AS [08年3月],
A.a4*1000.0/B.a4 AS [08年4月],
A.a5*1000.0/B.a5 AS [08年5月],
A.a6*1000.0/B.a6 AS [08年6月]
FROM ly_mount AS A
JOIN ly_shix AS B
ON A.类别=B.类别
上述程序中,其中分母有为0的情况,SQL SERVER报被零除错误,如何避免这个情况>????如果分母为0,最好是计算结果为0或是NULL就可以了。
AS
SELECT
A.类别,
(case b.a1 when 0 then 0 else A.a1*1000.0/B.a1 end) AS [08年1月],
(case b.a2 when 0 then 0 else A.a2*1000.0/B.a2 end) AS [08年2月],
(case b.a3 when 0 then 0 else A.a3*1000.0/B.a3 end) AS [08年3月],
(case b.a4 when 0 then 0 else A.a4*1000.0/B.a4 end) AS [08年4月],
(case b.a5 when 0 then 0 else A.a5*1000.0/B.a5 end) AS [08年5月],
(case b.a6 when 0 then 0 else A.a6*1000.0/B.a6 end) AS [08年6月]
FROM ly_mount AS A
JOIN ly_shix AS B
ON A.类别=B.类别
AS
SELECT
A.类别,
A.a1*1000.0/case when (B.a1 = 0 or B.a1 is null) then 1 else B.a1 end AS [08年1月],
A.a2*1000.0/case when (B.a2 = 0 or B.a2 is null) then 1 else B.a2 end AS [08年2月],
A.a3*1000.0/case when (B.a3 = 0 or B.a3 is null) then 1 else B.a3 end AS [08年3月],
A.a4*1000.0/case when (B.a4 = 0 or B.a4 is null) then 1 else B.a4 end AS [08年4月],
A.a5*1000.0/case when (B.a5 = 0 or B.a5 is null) then 1 else B.a5 end AS [08年5月],
A.a6*1000.0/case when (B.a6 = 0 or B.a6 is null) then 1 else B.a6 end AS [08年6月]
FROM ly_mount AS A
JOIN ly_shix AS B
ON A.类别=B.类别
AS
SELECT
A.类别,
(case b.a1 when 0 then 0 else A.a1*1000.0/B.a1 end) AS [08年1月],
(case b.a2 when 0 then 0 else A.a2*1000.0/B.a2 end) AS [08年2月],
(case b.a3 when 0 then 0 else A.a3*1000.0/B.a3 end) AS [08年3月],
(case b.a4 when 0 then 0 else A.a4*1000.0/B.a4 end) AS [08年4月],
(case b.a5 when 0 then 0 else A.a5*1000.0/B.a5 end) AS [08年5月],
(case b.a6 when 0 then 0 else A.a6*1000.0/B.a6 end) AS [08年6月]
FROM ly_mount AS A
JOIN ly_shix AS B
ON A.类别=B.类别 CREATE VIEW dbo.MAV_ly
AS
SELECT
A.类别,
(case when b.a1 = 0 then 0 else A.a1*1000.0/B.a1 end) AS [08年1月],
(case when b.a2 = 0 then 0 else A.a2*1000.0/B.a2 end) AS [08年2月],
(case when b.a3 = 0 then 0 else A.a3*1000.0/B.a3 end) AS [08年3月],
(case when b.a4 = 0 then 0 else A.a4*1000.0/B.a4 end) AS [08年4月],
(case when b.a5 = 0 then 0 else A.a5*1000.0/B.a5 end) AS [08年5月],
(case when b.a6 = 0 then 0 else A.a6*1000.0/B.a6 end) AS [08年6月]
FROM ly_mount AS A
JOIN ly_shix AS B
ON A.类别=B.类别
CREATE VIEW dbo.MAV_ly
AS
SELECT
A.类别,
(case when B.a1=0 then 0 else A.a1*1000.0/B.a1 end ) [08年1月],
(case when B.a2=0 then 0 else A.a1*1000.0/B.a2 end ) [08年2月],
(case when B.a3=0 then 0 else A.a1*1000.0/B.a3 end )[08年3月],
(case when B.a4=0 then 0 else A.a1*1000.0/B.a4 end )[08年4月],
(case when B.a5=0 then 0 else A.a1*1000.0/B.a5 end )[08年5月],
(case when B.a6=0 then 0 else A.a1*1000.0/B.a6 end ) [08年6月]
FROM ly_mount AS A
JOIN ly_shix AS B
ON A.类别=B.类别
然后用上面的case when的格式
AS
SELECT
A.类别,
(case b.a1 when 0 then 0 else A.a1*1000.0/B.a1 end) AS [08年1月],
(case b.a2 when 0 then 0 else A.a2*1000.0/B.a2 end) AS [08年2月],
(case b.a3 when 0 then 0 else A.a3*1000.0/B.a3 end) AS [08年3月],
(case b.a4 when 0 then 0 else A.a4*1000.0/B.a4 end) AS [08年4月],
(case b.a5 when 0 then 0 else A.a5*1000.0/B.a5 end) AS [08年5月],
(case b.a6 when 0 then 0 else A.a6*1000.0/B.a6 end) AS [08年6月]
FROM ly_mount AS A
JOIN ly_shix AS B
ON A.类别=B.类别
AS
SELECT
A.类别,
A.a1*1000.0/nullif(B.a1,0) AS [08年1月],
A.a2*1000.0/nullif(B.a2,0) AS [08年2月],
A.a3*1000.0/nullif(B.a3,0) AS [08年3月],
A.a4*1000.0/nullif(B.a4,0) AS [08年4月],
A.a5*1000.0/nullif(B.a5,0) AS [08年5月],
A.a6*1000.0/nullif(B.a6,0) AS [08年6月]
FROM ly_mount AS A
JOIN ly_shix AS B
ON A.类别=B.类别
AS
SELECT
A.类别,
(case b.a1 when 0 then 0 else A.a1*1000.0/B.a1 end) AS [08年1月],
(case b.a2 when 0 then 0 else A.a2*1000.0/B.a2 end) AS [08年2月],
(case b.a3 when 0 then 0 else A.a3*1000.0/B.a3 end) AS [08年3月],
(case b.a4 when 0 then 0 else A.a4*1000.0/B.a4 end) AS [08年4月],
(case b.a5 when 0 then 0 else A.a5*1000.0/B.a5 end) AS [08年5月],
(case b.a6 when 0 then 0 else A.a6*1000.0/B.a6 end) AS [08年6月]
FROM ly_mount AS A
JOIN ly_shix AS B
ON A.类别=B.类别