sqlserver2005添加了带有总计的行

Question

我有一个SQL SERVER 2005查询，我需要在末尾添加一行，其中包含一些列的总数。我尝试过使用"rollup"，但这需要对查询进行分组。我也试着把所有的东西都塞进一个子查询中，但我做不到。有人能给我一些主意吗？列必须合计，都是"IMP"。谢谢

SELECT C.INS,C.TIM,C.OIM,C.SIM,C.NIM,C.FEC AS FECHA_FACT,C.CCC,C.SUC,C.NRO,C.IMP AS TOTAL_FACT,
ISNULL(C1.IMP,0) AS PAGO_INST,ISNULL(C2.IMP,0) AS NOTA_CREDITO,ISNULL(C3.IMP,0) AS RET_GANANCIAS,
ISNULL(C4.IMP,0) AS RET_ING_BRU,ISNULL(C6.IMP,0) AS RET_IVA,   ISNULL(C5.IMP,0) AS DEBITO_FECLIBA,
ISNULL(C7.IMP,0) AS RET_SUSS
,(C.IMP + ISNULL(C1.IMP,0) + ISNULL(C2.IMP,0) + ISNULL(C3.IMP,0) + ISNULL(C4.IMP,0) + ISNULL(C5.IMP,0)+ ISNULL(C6.IMP,0) + ISNULL(C7.IMP,0)) AS SALDO
FROM CLICTA C 
LEFT JOIN CLICTA C1 ON C.INS = C1.INS AND C1.CCC = 'PI' AND C.NIM = C1.NIM
LEFT JOIN CLICTA C2 ON C.INS = C2.INS AND C2.CCC = 'cp' AND C.NIM = C2.NIM
LEFT JOIN CLICTA C3 ON C.INS = C3.INS AND C3.CCC = 'R1' AND C.NIM = C3.NIM
LEFT JOIN CLICTA C4 ON C.INS = C4.INS AND C4.CCC = 'R2' AND C.NIM = C4.NIM
LEFT JOIN CLICTA C5 ON C.INS = C5.INS AND C5.CCC = 'R5' AND C.NIM = C5.NIM
LEFT JOIN CLICTA C6 ON C.INS = C6.INS AND C6.CCC = 'R6' AND C.NIM = C6.NIM
LEFT JOIN CLICTA C7 ON C.INS = C7.INS AND C7.CCC = 'S1' AND C.NIM = C7.NIM
WHERE C.INS BETWEEN '011I' AND '011I'  --INSTITUCION         
AND C.TIM BETWEEN 'fp' AND 'fp'     --TIPO COMPROBANTE
AND C.OIM BETWEEN '' AND ''         --ORIGEN
AND C.SIM BETWEEN '100' AND '100'   --SUCURSAL        
AND C.NIM BETWEEN '4370' AND '4395'  --DESDE HASTA COMPROBANTE            
AND C.CCC = 'fp' --TIPO COMPROBANTE
AND C.IMP <> 0
ORDER BY C.NIM

Answer 1

您可以使用cte在union语句中重用查询：

;with cte as(current quety)
select *, 1 as ordering from cte
union
select ..., null, sum(), null ,..., 2 as ordering from cte
group by ...
order by ordering