select gysmc,clmc,sum(sl)[sl] into #a from test2
group by gysmc,clmc with rollup
update #a set clmc=case when len(isnull(gysmc,''))=0 then '总计'
else isnull(clmc,'小计') end,gysmc=isnull(clmc,'')
select * from #a
drop table #a
SELECT CASE WHEN (GROUPING(gysmc) = 1) THEN '总计'
ELSE ISNULL(gysmc, 'UNKNOWN')
END AS gysmc,
CASE WHEN (GROUPING(clmc) = 1) THEN ''
ELSE ISNULL(clmc, 'UNKNOWN')
END AS clmc,
SUM(sl) AS sl
FROM 你的表
GROUP BY gysmc, clmc WITH ROLLUP
select gysmc ,clmc,sl
from
(
select * ,gysmc as flag1 from c
union all
select gysmc,' ',sum( sl) sl ,gysmc from c group by gysmc
union all
select '总计',' ',sum(sl) sl,' ' from c
) b
order by flag1 desc
select gysmc ,clmc,sl
from
(
select * ,gysmc as flag1 from c
union all
select gysmc,' ',sum( sl) sl ,gysmc from c group by gysmc
union all
select '总计',' ',sum(sl) sl,' ' from c
) b
order by flag1 desc
SELECT * FROM table1
UNION
SELECT gysmc,'' AS clmc,sl FROM (SELECT gysmc,SUM(sl) AS sl FROM table1)
UNION
SELECT '总计' AS gysmc,'' AS clmc,sl FROM (SELECT SUM(sl) AS FROM table1)
ORDER BY 1,2