表结构如下:
表A
id count
a 1
a 2
c 1
d 2
b 1
c 2结果:
id count
a 3
c 3
d 2
b 1
id 有重复,我现在想把 重复的如"a" 把它的count想加,得到上面的结果表,我这样描述的清楚么?通过一个SQL
表A
id count
a 1
a 2
c 1
d 2
b 1
c 2结果:
id count
a 3
c 3
d 2
b 1
id 有重复,我现在想把 重复的如"a" 把它的count想加,得到上面的结果表,我这样描述的清楚么?通过一个SQL
go
create table tb([id] varchar(10),[count] int)
insert tb select 'a',1
union all select 'a',2
union all select 'c',1
union all select 'd',2
union all select 'b',1
union all select 'c',2
go
select id,sum([count]) [count] from tb group by id
/*
id count
---------- -----------
a 3
b 1
c 3
d 2(4 行受影响)
*/
insert into @a select 'a',1
union all select 'a',2
union all select 'c',1
union all select 'd',2
union all select 'b',1
union all select 'c',2
select id,sum([count]) as count from @a
group by id order by [count] desc
id count
---------- -----------
a 3
c 3
d 2
b 1(4 行受影响)
goinsert test select 'a' , 1
insert test select 'a' , 2
insert test select 'c' , 1
insert test select 'd' , 2
insert test select 'b' , 1
insert test select 'c' , 2
go
select id ,sum([count]) [count] from test group by id order by id
go
drop table test
/*
id count
----- -----------
a 3
b 1
c 3
d 2(所影响的行数为 4 行)
*/
这有100分啊!哈哈
表A
id count
a 1
a 2
c 1
d 2
b 1
c 2 结果:
id count
a 3
c 3
d 2
b 1 select id , sum(count) as count from 表A group by id
go
create table tb([id] varchar(10),[count] int)
insert tb select 'a',1
union all select 'a',2
union all select 'c',1
union all select 'd',2
union all select 'b',1
union all select 'c',2
go
select id,sum([count]) [count] from tb group by id order by [count] desc
/*
id count
---------- -----------
a 3
c 3
d 2
b 1(4 行受影响)
*/调整一下.
select id,sum(count) from a group by id order by id
select id , sum(count) as count
from 表A
group by id
order by sum(count) desc
if object_id('test') is not null drop table test
create table test (id varchar(10), count int)--插入数据
insert into test (id,count)
select 'a', 1 union all
select 'a', 2 union all
select 'c', 1 union all
select 'd', 2 union all
select 'b', 1 union all
select 'c', 2
--结果
Select [id], sum([count]) as Result From test group by [id] --删除测试数据
drop table test
select id,cnt=sum([count]) from A group by id
select id, sum([count]) as cnt from A group by id
select id,sum(count) as count
from 表A
group by id
order by sum(count) desc
from 表A
group by id
order by sum(count) desc
绝对的好用,请给分
declare @A (id nvarchar(5),count int)
--测试数据-----------
insert into @A
select 'a', 1 union all
select 'a', 2 union all
select 'c', 1 union all
select 'd', 2 union all
select 'b', 1 union all
select 'c', 2
----------------------select id,sum(count) from A group by id/*
结果
a 3
b 1
c 3
d 2
*/
select id,sum(1) [count] from A group by id
select id , sum(count) as count from 表A group by id