小计与合合计

[chenam]

一张表有如下字段
gysmc(供应商名称),clmc（材料名称）,sl(数量)
我想得到如下效果。

公司一 ,材料1，20
公司一，材料2，10
公司一　　　　　30
公司二，材料1，5
公司二　　　　5

怎样写sql 语句呀？谢谢了。

[chenam]

公司一 ,材料1，20
公司一，材料2，10
公司一　　　　　30
公司二，材料1，5
公司二　　　　5
总计 35

[伍子V5]

select gysmc as 供应商名称，clmc as 材料名称，sum(sl) as 合计
from 表
group by gysmc,clmc with cube

[lfangxiao]

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

[lfangxiao]

对了，这样排序不行，自己增加一个排序的字段吧

[lusemaomaochong]

select 公司,材料,sum(sl) from table where 条件 group by 公司,材料 with rollup

[happydreamer]

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

[happydreamer]

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

[lfangxiao]

用这条语句也可以，刚刚那条太复杂了：

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

[meng___xuan]

好像下面的办法不行，你们查血的时候没有过滤，会出现很多没有的纪录，你们看看。我感觉还是剑琴搂住得不错。UP.

[老宛]

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