if object_id('pubs..tb') is not null drop table tb gocreate table tb ( dep char(1), number int )insert into tb(dep,number) values('A', 1) insert into tb(dep,number) values('B', 2) insert into tb(dep,number) values('C', 2) insert into tb(dep,number) values('A', 3) insert into tb(dep,number) values('C', 1) insert into tb(dep,number) values('B', 5)select m.dep , m.depcount , total = (select sum(number) from tb) from ( select dep , sum(number) as depcount from tb group by dep ) mdrop table tb dep depcount total ---- ----------- ----------- A 4 14 B 7 14 C 3 14(所影响的行数为 3 行)
if object_id('pubs..tb') is not null drop table tb gocreate table tb ( dep char(1), number int )insert into tb(dep,number) values('A', 1) insert into tb(dep,number) values('B', 2) insert into tb(dep,number) values('C', 2) insert into tb(dep,number) values('A', 3) insert into tb(dep,number) values('C', 1) insert into tb(dep,number) values('B', 5)select dep , depcount , total = (select sum(number) from tb) from ( select dep , sum(number) as depcount from tb group by dep ) tdrop table tb dep depcount total ---- ----------- ----------- A 4 14 B 7 14 C 3 14(所影响的行数为 3 行)
if object_id('pubs..tb') is not null drop table tb gocreate table tb ( dep char(1), number int )insert into tb(dep,number) values('A', 1) insert into tb(dep,number) values('B', 2) insert into tb(dep,number) values('C', 2) insert into tb(dep,number) values('A', 3) insert into tb(dep,number) values('C', 1) insert into tb(dep,number) values('B', 5)select dep , sum(number) as depcount , total = (select sum(number) from tb) from tb group by depdrop table tb dep depcount total ---- ----------- ----------- A 4 14 B 7 14 C 3 14(所影响的行数为 3 行)
create table T(dep char(1), number int) insert T select 'A', 1 union all select 'B', 2 union all select 'C', 2 union all select 'A', 3 union all select 'C', 1 union all select 'B', 5select dep, sum(number) as depcount,total=(select sum(number) from T) from T group by dep --result dep depcount total ---- ----------- ----------- A 4 14 B 7 14 C 3 14(3 row(s) affected)
drop table tb
gocreate table tb
(
dep char(1),
number int
)insert into tb(dep,number) values('A', 1)
insert into tb(dep,number) values('B', 2)
insert into tb(dep,number) values('C', 2)
insert into tb(dep,number) values('A', 3)
insert into tb(dep,number) values('C', 1)
insert into tb(dep,number) values('B', 5)select m.dep , m.depcount , total = (select sum(number) from tb) from
(
select dep , sum(number) as depcount from tb group by dep
) mdrop table tb
dep depcount total
---- ----------- -----------
A 4 14
B 7 14
C 3 14(所影响的行数为 3 行)
drop table tb
gocreate table tb
(
dep char(1),
number int
)insert into tb(dep,number) values('A', 1)
insert into tb(dep,number) values('B', 2)
insert into tb(dep,number) values('C', 2)
insert into tb(dep,number) values('A', 3)
insert into tb(dep,number) values('C', 1)
insert into tb(dep,number) values('B', 5)select dep , depcount , total = (select sum(number) from tb) from
(
select dep , sum(number) as depcount from tb group by dep
) tdrop table tb
dep depcount total
---- ----------- -----------
A 4 14
B 7 14
C 3 14(所影响的行数为 3 行)
drop table tb
gocreate table tb
(
dep char(1),
number int
)insert into tb(dep,number) values('A', 1)
insert into tb(dep,number) values('B', 2)
insert into tb(dep,number) values('C', 2)
insert into tb(dep,number) values('A', 3)
insert into tb(dep,number) values('C', 1)
insert into tb(dep,number) values('B', 5)select dep , sum(number) as depcount , total = (select sum(number) from tb)
from tb
group by depdrop table tb
dep depcount total
---- ----------- -----------
A 4 14
B 7 14
C 3 14(所影响的行数为 3 行)
create table T(dep char(1), number int)
insert T select 'A', 1
union all select 'B', 2
union all select 'C', 2
union all select 'A', 3
union all select 'C', 1
union all select 'B', 5select dep, sum(number) as depcount,total=(select sum(number) from T) from T group by dep
--result
dep depcount total
---- ----------- -----------
A 4 14
B 7 14
C 3 14(3 row(s) affected)