这条语句应该怎么写才能不出现 with rollup错误(100分)

SELECT qk, sspjdy = (CASE WHEN SUM(shsr) &gt; SUM(yycb) <br>&nbsp; &nbsp; &nbsp; THEN '效益一类' WHEN SUM(shsr) &gt;= SUM(sccb) AND SUM(shsr) &lt; SUM(yycb) <br>&nbsp; &nbsp; &nbsp; THEN '效益二类' WHEN SUM(shsr) &gt;= SUM(czcb) AND SUM(shsr) &lt; SUM(sccb) <br>&nbsp; &nbsp; &nbsp; THEN '效益三类' WHEN SUM(shsr) &lt; SUM(czcb) THEN '无效益' ELSE '不确定' END), <br>&nbsp; &nbsp; &nbsp; SUM(zjclf) AS A1, SUM(zjrlf) AS A2<br>FROM ydtable a<br>WHERE ny = '200712'<br>GROUP BY qk WITH rollup<br>HAVING (CASE WHEN SUM(shsr) &gt; SUM(yycb) THEN '效益一类' WHEN SUM(shsr) <br>&nbsp; &nbsp; &nbsp; &gt;= SUM(sccb) AND SUM(shsr) &lt; SUM(yycb) THEN '效益二类' WHEN SUM(shsr) <br>&nbsp; &nbsp; &nbsp; &gt;= SUM(czcb) AND SUM(shsr) &lt; SUM(sccb) THEN '效益三类' WHEN SUM(shsr) <br>&nbsp; &nbsp; &nbsp; &lt; SUM(czcb) THEN '无效益' ELSE '不确定' END) = '效益一类'

你这样写：<br>Select rt.qk, rt.sspjdy, rt.A1, rt.A2 From<br>(SELECT qk, <br>CASE WHEN SUM(shsr) &gt; SUM(yycb) THEN '效益一类' <br>&nbsp; WHEN SUM(shsr) &gt;= SUM(sccb) AND SUM(shsr) &lt; SUM(yycb) THEN '效益二类' <br>&nbsp; WHEN SUM(shsr) &gt;= SUM(czcb) AND SUM(shsr) &lt; SUM(sccb) THEN '效益三类'<br>&nbsp; WHEN SUM(shsr) &lt; SUM(czcb) THEN '无效益' ELSE '不确定' END sspjdy, <br>SUM(zjclf) AS A1, SUM(zjrlf) AS A2<br>FROM ydtable a<br>WHERE ny = '200712'<br>GROUP BY qk) rt<br>Where sspjdy = '效益一类'

to szhcracker<br>你把with rollup去掉了，我的那段代码，去掉with rollup也能执行<br>关键是我想在最后一行进行汇总，但去掉with rollup就没有汇总行了

谁能帮帮忙

SELECT qk, sspjdy = (CASE WHEN SUM(shsr) &gt; SUM(yycb) <br>&nbsp; &nbsp; &nbsp; THEN '效益一类' WHEN SUM(shsr) &gt;= SUM(sccb) AND SUM(shsr) &lt; SUM(yycb) <br>&nbsp; &nbsp; &nbsp; THEN '效益二类' WHEN SUM(shsr) &gt;= SUM(czcb) AND SUM(shsr) &lt; SUM(sccb) <br>&nbsp; &nbsp; &nbsp; THEN '效益三类' WHEN SUM(shsr) &lt; SUM(czcb) THEN '无效益' ELSE '不确定' END), <br>&nbsp; &nbsp; &nbsp; SUM(zjclf) AS A1, SUM(zjrlf) AS A2<br>FROM ydtable a WITH rollup<br>WHERE ny = '200712'<br>GROUP BY qk <br>HAVING (CASE WHEN SUM(shsr) &gt; SUM(yycb) THEN '效益一类' WHEN SUM(shsr) <br>&nbsp; &nbsp; &nbsp; &gt;= SUM(sccb) AND SUM(shsr) &lt; SUM(yycb) THEN '效益二类' WHEN SUM(shsr) <br>&nbsp; &nbsp; &nbsp; &gt;= SUM(czcb) AND SUM(shsr) &lt; SUM(sccb) THEN '效益三类' WHEN SUM(shsr) <br>&nbsp; &nbsp; &nbsp; &lt; SUM(czcb) THEN '无效益' ELSE '不确定' END) = '效益一类' &nbsp;WITH rollup

SELECT qk, sspjdy = (CASE WHEN SUM(shsr) &gt; SUM(yycb) <br>&nbsp; &nbsp; &nbsp; THEN '效益一类' WHEN SUM(shsr) &gt;= SUM(sccb) AND SUM(shsr) &lt; SUM(yycb) <br>&nbsp; &nbsp; &nbsp; THEN '效益二类' WHEN SUM(shsr) &gt;= SUM(czcb) AND SUM(shsr) &lt; SUM(sccb) <br>&nbsp; &nbsp; &nbsp; THEN '效益三类' WHEN SUM(shsr) &lt; SUM(czcb) THEN '无效益' ELSE '不确定' END), <br>&nbsp; &nbsp; &nbsp; SUM(zjclf) AS A1, SUM(zjrlf) AS A2<br>FROM ydtable a WITH rollup<br>WHERE ny = '200712'<br>GROUP BY qk <br>HAVING (CASE WHEN SUM(shsr) &gt; SUM(yycb) THEN '效益一类' WHEN SUM(shsr) <br>&nbsp; &nbsp; &nbsp; &gt;= SUM(sccb) AND SUM(shsr) &lt; SUM(yycb) THEN '效益二类' WHEN SUM(shsr) <br>&nbsp; &nbsp; &nbsp; &gt;= SUM(czcb) AND SUM(shsr) &lt; SUM(sccb) THEN '效益三类' WHEN SUM(shsr) <br>&nbsp; &nbsp; &nbsp; &lt; SUM(czcb) THEN '无效益' ELSE '不确定' END) = '效益一类'

to:3868474<br>还是不行，只要在case when中出现sum，就会提示 with rollup错误

不知道下面这样行不行：<br>Select rt.qk, rt.sspjdy, rt.A1, rt.A2 From<br>(SELECT qk, <br>CASE WHEN SUM(shsr) &gt; SUM(yycb) THEN '效益一类' <br>&nbsp; WHEN SUM(shsr) &gt;= SUM(sccb) AND SUM(shsr) &lt; SUM(yycb) THEN '效益二类' <br>&nbsp; WHEN SUM(shsr) &gt;= SUM(czcb) AND SUM(shsr) &lt; SUM(sccb) THEN '效益三类'<br>&nbsp; WHEN SUM(shsr) &lt; SUM(czcb) THEN '无效益' ELSE '不确定' END sspjdy, <br>SUM(zjclf) AS A1, SUM(zjrlf) AS A2<br>FROM ydtable a WITH rollup<br>WHERE ny = '200712'<br>GROUP BY qk) rt<br>Where sspjdy = '效益一类'

我先把分散了，虽然没有能够解决，但是还要谢谢你们！<br><br>to:szhcracker<br>这样也是不行的<br>只要是在case when中包含聚合函数，使用with rollup时都会出错，这是sqlserver的一个bug<br><br>我刚才从微软的suppert中找到了解决方法,分两步进行才可以，必须用到内存表或者视图才能完成，下面的方法就可以<br>第一步：<br>SELECT qk, (CASE WHEN SUM(shsr) &gt; SUM(yycb) THEN '效益一类' WHEN SUM(shsr) <br>&nbsp; &nbsp; &nbsp; &gt;= SUM(sccb) AND SUM(shsr) &lt; SUM(yycb) THEN '效益二类' WHEN SUM(shsr) <br>&nbsp; &nbsp; &nbsp; &gt;= SUM(czcb) AND SUM(shsr) &lt; SUM(sccb) THEN '效益三类' WHEN SUM(shsr) <br>&nbsp; &nbsp; &nbsp; &lt; SUM(czcb) THEN '无效益' ELSE '不确定' END) AS xylb, SUM(zjclf) AS zjclf, <br>&nbsp; &nbsp; &nbsp; SUM(zjrlf) AS zjrlf<br>INTO #temp1<br>FROM ydtable<br>GROUP BY qk<br>第二步：<br>select qk,xylb,sum(zjclf),sum(zjrlf)<br>from [#temp1]<br>group by xylb,qk with rollup