sql高手进，疑难问题！100分

[zgplyl]

环境：DB(数据库版本): Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86) Nov 24 2008 13:01:59 Copyright (c) 1988-2005 Microsoft Corporation Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 2)


问题描述和想要结果：（简单描述）

请看图，我用的sql语句，group by with rollup（具体代码帖到最下面）
如果“派工状态”为预安排 ，则未回报数量为0（实际第一行未回报数量为2）（这个地方sql 用的case when）
这样以后就有问题了，小计，合计都不准确了，多加了2.小计应该为50，这是想要的结果。
告诉我思路，有例子也行！


代码如下（delphi描述）：

ADOQuery1.SQL.Text := 'select case when grouping(dbo.MyView1.FName)=1 '
+' and grouping(dbo.MyView1.FNumber)=1 '
+' and grouping(dbo.MyView1.Expr4)=1 '
+' and grouping(dbo.MyView1.FStatus)=1 '
+' and grouping(dbo.MyView1.FModel)=1 '
+' and grouping(dbo.MyView1.Expr2)=1 '
+' and grouping(dbo.MyView1.FNote)=1 '
+' and grouping(dbo.MyView1.Expr1)=1 '
+' and grouping(dbo.MyView1.FICMOBillNo)=1 then ''合计'' else dbo.MyView1.FICMOBillNo end 生产任务单号,'
+'case when grouping(dbo.MyView1.FName)=0 '
+' and grouping(dbo.MyView1.FNumber)=1 '
+' and grouping(dbo.MyView1.Expr4)=1 '
+' and grouping(dbo.MyView1.FStatus)=1 '
+' and grouping(dbo.MyView1.FModel)=1 '
+' and grouping(dbo.MyView1.Expr2)=1 '
+' and grouping(dbo.MyView1.FNote)=1 '
+' and grouping(dbo.MyView1.Expr1)=1 '
+' and grouping(dbo.MyView1.FICMOBillNo)=1 then ''小计'' else dbo.MyView1.FModel end 规格型号,'
+'dbo.MyView1.FNote AS 任务优先顺序,'
+'dbo.MyView1.Expr2 as 工序名称,'
+'case when dbo.MyView1.FModel = ''小计'' then ''null'' else dbo.MyView1.FName end as 设备名称,'
+'dbo.MyView1.FNumber AS 资源代码,'
+'dbo.MyView1.Expr4 AS 操作工,'
+'Case When dbo.MyView1.FStatus=''0'' and dbo.MyView1.FModel != ''小计'' then ''预安排'' when dbo.MyView1.FStatus=''1'' and dbo.MyView1.FModel != ''小计'' then ''已开工'' else '' '' end as 派工状态,'
+'sum(dbo.MyView1.FAuxQtyPlan) as 派工数量,'
+'sum(dbo.MyView1.FAuxQtyPass) as 合格数量,'
+'sum(dbo.MyView1.FAuxQtyScrap) as 因工报废数量,'
+'sum(dbo.MyView1.FAuxQtyForItem) as 因料报废数量,'
+'sum(dbo.MyView1.FAuxReprocessedQty) as 返修数量,'
+'sum(dbo.MyView1.FAuxQtyForItem + dbo.MyView1.FAuxQtyScrap + dbo.MyView1.FAuxReprocessedQty + dbo.MyView1.FAuxQtyPass) as 汇报数量,'
+'case when dbo.MyView1.FStatus=0 then 0 '
+' else sum(dbo.MyView1.FAuxQtyPlan - dbo.MyView1.FAuxQtyForItem - dbo.MyView1.FAuxQtyScrap - dbo.MyView1.FAuxReprocessedQty - dbo.MyView1.FAuxQtyPass) end as 未汇报数量,'
+'dbo.MyView1.Expr1 AS 所属工作中心'
+' from MyView1 '
+' where '+SelectWord+' like '+quotedstr('%'+edit4.Text+'%')
+' and dbo.MyView1.FAuxQtyPlan - dbo.MyView1.FAuxQtyForItem - dbo.MyView1.FAuxQtyScrap - dbo.MyView1.FAuxReprocessedQty - dbo.MyView1.FAuxQtyPass >0'
+' and dbo.MyView1.FStatus like '+quotedstr('%'+ShenHeWord+'%')
+' and dbo.MyView1.Expr3 like '+quotedstr('%'+ combobox2.Text +'%')
+' group by dbo.MyView1.FName,'
+'dbo.MyView1.FNumber,'
+'dbo.MyView1.Expr4,'
+'dbo.MyView1.FStatus,'
+'dbo.MyView1.FModel,'
+'dbo.MyView1.Expr2,'
+'dbo.MyView1.FNote,'
+'dbo.MyView1.Expr1,'
+'dbo.MyView1.FICMOBillNo with rollup ' //rollup
+' having grouping(dbo.MyView1.FName)=0 and grouping(dbo.MyView1.FNumber)=0 '
+' and grouping(dbo.MyView1.Expr4)=0 and grouping(dbo.MyView1.FStatus)=0 '
+' and grouping(dbo.MyView1.FModel)=0 and grouping(dbo.MyView1.Expr2)=0 '
+' and grouping(dbo.MyView1.FNote)=0 and grouping(dbo.MyView1.Expr1)=0 '
+' and grouping(dbo.MyView1.FICMOBillNo)=0 '
+' or grouping(dbo.MyView1.FName)=0 and grouping(dbo.MyView1.FNumber)=1 '
+' and grouping(dbo.MyView1.Expr4)=1 and grouping(dbo.MyView1.FStatus)=1 '
+' and grouping(dbo.MyView1.FModel)=1 and grouping(dbo.MyView1.Expr2)=1 '
+' and grouping(dbo.MyView1.FNote)=1 and grouping(dbo.MyView1.Expr1)=1 '
+' and grouping(dbo.MyView1.FICMOBillNo)=1 '
+' or grouping(dbo.MyView1.FName)=1 and grouping(dbo.MyView1.FNumber)=1 '
+' and grouping(dbo.MyView1.Expr4)=1 and grouping(dbo.MyView1.FStatus)=1 '
+' and grouping(dbo.MyView1.FModel)=1 and grouping(dbo.MyView1.Expr2)=1 '
+' and grouping(dbo.MyView1.FNote)=1 and grouping(dbo.MyView1.Expr1)=1 '
+' and grouping(dbo.MyView1.FICMOBillNo)=1 ';

[xiaodongni]

从代码里面没看出来为啥第一行的派工数量是2

[zgplyl]

是这样的，最开始结果是正确的，但现在业务需求要求如果“派工状态”为“预安排” ，则未汇报数量为0，通过以下语句实现， 'case when dbo.MyView1.FStatus=0 then 0 else sum(dbo.MyView1.FAuxQtyPlan - dbo.MyView1.FAuxQtyForItem - dbo.MyView1.FAuxQtyScrap - dbo.MyView1.FAuxReprocessedQty - dbo.MyView1.FAuxQtyPass) end as 未汇报数量,' 但是小计，合计没有变化，依然是原来的值

[spiritofdragon]

+'case when dbo.MyView1.FStatus=0 then 0 ' +' else sum(dbo.MyView1.FAuxQtyPlan - dbo.MyView1.FAuxQtyForItem - dbo.MyView1.FAuxQtyScrap - dbo.MyView1.FAuxReprocessedQty - dbo.MyView1.FAuxQtyPass) end as 未汇报数量,' 改成 +' sum(case when dbo.MyView1.FStatus=0 then 0 else dbo.MyView1.FAuxQtyPlan - dbo.MyView1.FAuxQtyForItem - dbo.MyView1.FAuxQtyScrap - dbo.MyView1.FAuxReprocessedQty - dbo.MyView1.FAuxQtyPass end) as 未汇报数量,' 试试。 意思就是，Sum在外，判断在内。

[xiaodongni]

引用 2 楼 zgplyl 的回复:

是这样的，最开始结果是正确的，但现在业务需求要求如果“派工状态”为“预安排” ，则未汇报数量为0，通过以下语句实现， 'case when dbo.MyView1.FStatus=0 then 0 else sum(dbo.MyView1.FAuxQtyPlan - dbo.MyView1.FAuxQtyForItem - dbo.MyView1.FAuxQtyScrap - dbo.MyView1.FAuxReprocessedQty - dbo.MyView1.FAuxQtyPass) end as 未汇报数量,' 但是小计，合计没有变化，依然是原来的值

派工数量那一年是对的了，本来就是2吗？ 只要修改后面的未汇报数量吗？ sum(dbo.MyView1.FAuxQtyPlan - dbo.MyView1.FAuxQtyForItem - dbo.MyView1.FAuxQtyScrap - dbo.MyView1.FAuxReprocessedQty - dbo.MyView1.FAuxQtyPass) end as 未汇报数量,'你用前面的总数减的，结果，前面没变，结果当然不会变了。

[zgplyl]

引用 3 楼 spiritofdragon 的回复:

+'case when dbo.MyView1.FStatus=0 then 0 ' +' else sum(dbo.MyView1.FAuxQtyPlan - dbo.MyView1.FAuxQtyForItem - dbo.MyView1.FAuxQtyScrap - dbo.MyView1.FAuxReprocessedQty - dbo.MyView1.FAuxQtyPass) end as 未汇报数量,' 改成 +' sum(case when dbo.MyView1.FStatus=0 then 0 else dbo.MyView1.FAuxQtyPlan - dbo.MyView1.FAuxQtyForItem - dbo.MyView1.FAuxQtyScrap - dbo.MyView1.FAuxReprocessedQty - dbo.MyView1.FAuxQtyPass end) as 未汇报数量,' 试试。 意思就是，Sum在外，判断在内。

谢谢，结果正确。但还是不太明白，晚上结贴。让大家再讨论一下。

[浪淘沙1997]

Sum在外，判断在内。 就這個原因 來晚了~