求SQL, 有点难度(300分)

select case when grouping(comput) = 1 then '合计' <br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; when grouping(ddbh) = 1 then '--' &nbsp;else comput end as 部件编号, <br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; case when grouping(ddbh) = 1 then '订单合计' <br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; else ddbh end as 订单编号, <br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; sum(ddsl) as 订单数量 &nbsp;from #temp<br>&nbsp; &nbsp; &nbsp; &nbsp; group by comput, ddbh with rollup <br><br><br>部件编号 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 订单编号 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;订单数量<br>500-315W01W030 &nbsp; LI072401 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 80<br>500-315W01W030 &nbsp; LI072880 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 86500<br>500-315W01W030 &nbsp; LI090729 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 51800<br>500-315W01W030 &nbsp; LI092852 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 13600<br>-- &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 订单合计 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 151980 有多条记录，返回这一行<br><br>500-315W01W040 &nbsp; LI092923 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 3110<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 订单合计 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 3110 &nbsp;只有一条记录，不想返回这一行，怎么做？<br>合计 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 订单合计 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 155090

不大明白你的意思，结果不是挺好的吗？

我的意思是，只有条记录，何必多此一举，还来个汇总？<br>有没有其他方法？不一定用 With Rollup？

在select中增加一个统计列，统计comput下的不同ddbh数，然后，在外面用一个Select <br>Where 包围过滤，如下所示：<br><br>Select 部件编号, 订单编号, 订单数量<br>From (<br>&nbsp; select case when grouping(comput) = 1 then '合计' <br>&nbsp; &nbsp; when grouping(ddbh) = 1 then '--' &nbsp;else comput end as 部件编号, <br>&nbsp; &nbsp; case when grouping(ddbh) = 1 then '订单合计' <br>&nbsp; &nbsp; else ddbh end as 订单编号, <br>&nbsp; &nbsp; sum(ddsl) as 订单数量,<br>&nbsp; &nbsp; (SELECT COUNT(DISTINCT ddbh) FROM #temp WHERE comput = a.comput) AS C<br>&nbsp; from #temp a<br>&nbsp; group by comput, ddbh with rollup<br>&nbsp; )<br>WHERE not ( C=1 and 部件编号='--' ) &nbsp; &nbsp;/*去除汇总中的针对单行汇总情况*/

谢谢前辈。