access中的IIF改成sqlserver的问题???

[fleamboy]

access中的下列语句:
SELECT W.ID, W.TreeParent, W.UserCode,w.Unit,w.Unit2,w.Scale, W.Name, W.Sort,
Sum(WS.Number) AS SumNumber,Sum(WS.Total) AS SumTotal ,
iif(SumNumber=0,0,SumTotal/SumNumber) as Price
FROM Ware AS W INNER JOIN WareStock AS WS ON W.ID = WS.WareID
GROUP BY W.ID, W.TreeParent, W.UserCode,w.Unit,w.Unit2,w.Scale, W.Name, W.Sort

我改成SQLserver的语句,但是提示错误,因改如何改啊???
SELECT W.ID, W.TreeParent, W.UserCode,w.Unit,w.Unit2,w.Scale, W.Name, W.Sort,
Sum(WS.Number) SumNumber,Sum(WS.Total) SumTotal ,
(case when SumNumber=0 then 0 else SumTotal/SumNumber end) Price
FROM Ware W INNER JOIN WareStock WS ON W.ID = WS.WareID
GROUP BY W.ID, W.TreeParent, W.UserCode,w.Unit,w.Unit2,w.Scale, W.Name, W.Sort

错误提示:
服务器: 消息 207，级别 16，状态 3，行 1
列名 'SumNumber' 无效。
服务器: 消息 207，级别 16，状态 1，行 1
列名 'SumTotal' 无效。
服务器: 消息 207，级别 16，状态 1，行 1
列名 'SumNumber' 无效。

[---涛声依旧---]

用case ... when ...代替 iif

[netcoder]

try:

SELECT W.ID, W.TreeParent, W.UserCode,w.Unit,w.Unit2,w.Scale, W.Name, W.Sort,
Sum(WS.Number) SumNumber,Sum(WS.Total) SumTotal ,
(case SumNumber when 0 then 0 else SumTotal/SumNumber end) Price
FROM Ware W INNER JOIN WareStock WS ON W.ID = WS.WareID
GROUP BY W.ID, W.TreeParent, W.UserCode,w.Unit,w.Unit2,w.Scale, W.Name, W.Sort

[fleamboy]

还是一样的提示,好像Sum(WS.Number) SumNumber中同一SQL中不能使用SumNumber这个新的列!

[paoluo]

别名不能直接用。

改为这样试试：

Select *,(case when SumNumber=0 then 0 else SumTotal/SumNumber end) Price from (
SELECT W.ID, W.TreeParent, W.UserCode,w.Unit,w.Unit2,w.Scale, W.Name, W.Sort,
Sum(WS.Number) SumNumber,Sum(WS.Total) SumTotal
FROM Ware W INNER JOIN WareStock WS ON W.ID = WS.WareID
GROUP BY W.ID, W.TreeParent, W.UserCode,w.Unit,w.Unit2,w.Scale, W.Name, W.Sort) A