原代码,当SUM(table.aa)为零时执行异常:
SELECT
SUM((CASE WHEN table.bb = 0 THEN table.cc
WHEN table.bb <= '20110424' THEN table.dd
ELSE table.cc END) * table.aa
)
/SUM(table.aa)
AS jcc
FROM
table
-------------------------------------------------------------------------------------------------
新代码,需要对SUM(table.aa)执行0判断:
SELECT
(CASE SUM(table.aa) WHEN 0 THEN 0 ELSE
(
SUM((CASE WHEN table.bb = 0 THEN table.cc
WHEN table.bb <= '20110424' THEN table.dd
ELSE table.cc END) * table.aa
)
/SUM(table.aa)
)
END)
AS jcc
FROM
table