34,588
社区成员
发帖
与我相关
我的任务
分享
SELECT CASE WHEN name IS NULL THEN '小计' WHEN id IS NULL THEN '小计' ELSE id END id,
ISNULL(name,'') name,SUM([count]) [count],SUM(price) price
FROM #temp
GROUP BY id,name
WITH rollup
create table #temp(id varchar(10),name varchar(10),[count] int,price int,[status] varchar(5),XH varchar(10))
insert into #temp
select '070103','车床1','1','20','报废','PD300' union all
select '0801','计算机', '5', '60', '在用', '联想' union all
select '0501', '房屋', '6', '10', '在用', '无' union all
select '050102', '房屋1', '2', '20' , '在用', '无'union all
select '050106', '房屋2', '1', '20', '在用', '无'union all
select '0701', '车床' , '9', '10', '在用', 'AC500'
drop table #temp
select * from #temp
SELECT CASE WHEN name IS NULL THEN '小计' WHEN id IS NULL THEN '小计' ELSE id END id,
ISNULL(name,'') name,SUM([count]) [count],SUM(price) price
,isnull([status],'')[status],isnull(XH,'')XH
FROM #temp
GROUP BY id,name,[status],XH
WITH rollup