一个sql语句！怎么老是报错！在线等

[pweixing]

SELECT dbo.BS_ClassInfo.cDeptNo, dbo.BS_ClassInfo.cClassNo,
dbo.BS_ClassInfo.cClassName, dbo.BS_ClassInfo.istandard,
dbo.BS_ClassInfo.dStartTime, dbo.BS_ClassInfo.dEndTime,
dbo.FA_SignInfo.cSignAttrib, SUM(dbo.FA_SignInfo.iRatingP) AS iRatingP之Sum,
dbo.FA_SignInfo.iInsertP, SUM(dbo.FA_SignInfo.iRebateP) AS iRebateP之Sum,
SUM(dbo.FA_SignInfo.iAccountP) AS iAccountP之Sum, SUM(dbo.FA_SignInfo.iBackP)
AS iBackP之Sum,
iif(cSignAttrib = '退货' ,SUM(FA_SignInfo.iAccountP)，0 ) AS 退货,
SUM(dbo.FA_SignInfo.iRatingP)
- dbo.FA_SignInfo.iInsertP - SUM(dbo.FA_SignInfo.iRebateP)
- ISNULL(SUM(dbo.FA_SignInfo.iBackP), 0) AS 非退货,
dbo.FA_SignInfo.dSigTime
FROM dbo.BS_ClassInfo INNER JOIN
dbo.FA_SignInfo ON dbo.BS_ClassInfo.cClassNo = dbo.FA_SignInfo.cClassNo
GROUP BY dbo.BS_ClassInfo.cClassNo, dbo.BS_ClassInfo.cClassName,
dbo.BS_ClassInfo.istandard, dbo.BS_ClassInfo.dStartTime,
dbo.BS_ClassInfo.dEndTime, dbo.FA_SignInfo.cSignAttrib, dbo.FA_SignInfo.iInsertP,
dbo.FA_SignInfo.dSigTime, dbo.BS_ClassInfo.cDeptNo
having BS_ClassInfo.dStartTime<='2006-1-31 23:00:00' AND
BS_ClassInfo.dEndTime>='2006-1-1' AND FA_SignInfo.cSignAttrib<>'转走' AND FA_SignInfo.dSigTime<'2006-1-31 23:00:00'

请问上面这个语句是怎么错了？该怎么该？在线等！

[pweixing]

iif(cSignAttrib = '退货' ,SUM(FA_SignInfo.iAccountP)，0 ) AS 退货,
把这句去掉就好了，可是我需要统计这个数字。
当 cSignAttrib 字段为 退货 的时候，我需要把 iAccountP 字段进行求和。
还能不能用其他方式实现呢？

上面的语句在access里面通过，可是sql server 里面通过不了

[OracleRoob]

SQL Server 中使用Case when
不支持iif()

[OracleRoob]

sum(case cSignAttrib when '退货' then FA_SignInfo.iAccountP else 0)

[子陌红尘]

SELECT
a.cDeptNo,
a.cClassNo,
a.cClassName,
a.istandard,
a.dStartTime,
a.dEndTime,
b.cSignAttrib,
SUM(b.iRatingP) AS iRatingP之Sum,
b.iInsertP,
SUM(b.iRebateP) AS iRebateP之Sum,
SUM(b.iAccountP) AS iAccountP之Sum,
SUM(b.iBackP) AS iBackP之Sum,
SUM(case cSignAttrib when '退货' then b.iAccountP else 0 end) AS 退货,
SUM(b.iRatingP)
- b.iInsertP - SUM(b.iRebateP)
- ISNULL(SUM(b.iBackP), 0) AS 非退货,
b.dSigTime
FROM
dbo.BS_ClassInfo a
INNER JOIN
dbo.FA_SignInfo b
ON
a.cClassNo = b.cClassNo
GROUP BY
a.cClassNo, a.cClassName,
a.istandard, a.dStartTime,
a.dEndTime, b.cSignAttrib, b.iInsertP,
b.dSigTime, a.cDeptNo
having
a.dStartTime<='2006-1-31 23:00:00' AND a.dEndTime>='2006-1-1' AND b.cSignAttrib<>'转走' AND b.dSigTime<'2006-1-31 23:00:00'

[OracleRoob]

--少了个end
sum(case cSignAttrib when '退货' then FA_SignInfo.iAccountP else 0 end)

[yejianwind]

iif 是access中的,case when在sql server和ORACLE中都可以

[pweixing]

谢谢！马上揭帖

[pweixing]

不好意思，再顺便问一个问题，如果我想再加入一个条件做统计，这个条件该怎么写？

就是对 iRebateP 这个字段进行判断，大于0的做一个计数，小于0的做一个计数，最后查询结果能显示 销售和退货的记录个数。

我仿照刚才的 case 语句，可是还是写不好，大家再帮帮忙吧！

[Jane_64]

sum(case when iRebateP>0 then 1 else 0 end) AS 销售个数,
sum(case when iRebateP<0 then 1 else 0 end) AS 退货个数