select ID,'规模合计'=sum(规模),'资金合计'=sum(资金) from table1 where ID='1' group by ID
加标段,规模,资金,sum(资金),sum(规模)跟不加一样。
select ID,标段,规模,资金,'规模合计'=sum(规模),'资金合计'=sum(资金) from table1 where ID='1' group by ID,标段,规模,资金
加标段,规模,资金,sum(资金),sum(规模)跟不加一样。
select ID,标段,规模,资金,'规模合计'=sum(规模),'资金合计'=sum(资金) from table1 where ID='1' group by ID,标段,规模,资金
union
select ID,'sum',sum(规模) as 规模合计,sum(资金) as 资金合计 from table1 where ID='1' group by 标段
(select sum(A.规模) from table1 A where A.id=T.id ) AS 规模合计,
(select sum(B.资金) from table1 B where B.id=T.id ) AS 资金合计
from table1 T where ID='1'
加标段,规模,资金,sum(资金),sum(规模)跟不加一样。比较以下结果:
select ID,标段,规模,资金,'规模合计'=sum(规模),'资金合计'=sum(资金) from table1 where ID='1' group by ID,标段,规模,资金
select ID,标段,规模,资金, from table1 where ID='1' compute sum(规模),sum(资金) by id--加不加都可
详细可以看一下在线帮助关于group by 和compute的用法
from table1
where ID='1'
group by ID,标段,规模,资金
1 a 5 500
1 b 8 600
1 d 8 1000
合计 21 2100
from table1
where ID='1'
group by ID,标段,规模,资金
union all
select null,'合计',sum(规模),sum(资金)
from table1
where ID='1'
group by ID,标段,规模,资金
select ID,标段,规模,资金
from table1 where ID='1'
group by ID,标段,规模,资金
union all
select null,'合计',sum(规模),sum(资金)
from table1 where ID='1'
--下面是数据测试--测试数据
declare @t table(ID int,标段 varchar(10),规模 int,资金 int)
insert into @t
select 1,'a',5,500
union all select 1,'b',8,600
union all select 2,'e',3,250
union all select 1,'d',8,1000--第一种统计
select ID,标段,规模,资金,'规模合计'=sum(规模),'资金合计'=sum(资金)
from @t where ID='1'
group by ID,标段,规模,资金--第二种统计
select ID,标段,规模,资金
from @t where ID='1'
group by ID,标段,规模,资金
union all
select null,'合计',sum(规模),sum(资金)
from @t where ID='1'/*--测试结果
--统计1
ID 标段 规模 资金 规模合计 资金合计
----------- ---------- ----------- ----------- ----------- -----------
1 a 5 500 5 500
1 b 8 600 8 600
1 d 8 1000 8 1000(所影响的行数为 3 行)--统计2
ID 标段 规模 资金
----------- ---------- ----------- -----------
1 a 5 500
1 b 8 600
1 d 8 1000
NULL 合计 21 2100(所影响的行数为 4 行)
--*/