select id, name, sum(shuliang) as shuliang from table group by id,name order by shuliang desc
create table tb(id int,name varchar(10),shuliang int) insert into tb select 1,'a',1 insert into tb select 2,'b',1 insert into tb select 3,'c',2 insert into tb select 4,'d',4 insert into tb select 1,'a',1 insert into tb select 3,'c',3 insert into tb select 4,'d',1 insert into tb select 3,'c',2 go select id,name,sum(shuliang) as shuliang from tb group by id,name order by sum(shuliang) desc go drop table tb /* id name shuliang ----------- ---------- ----------- 3 c 7 4 d 5 1 a 2 2 b 1(4 行受影响) */
select id, name, sum(shuliang) as shuliang from tb order by sum(shuliang) desc
select id,name,sum(shuliang) as shuliang from ta group by id,name order by 3 desc
select id, name, sum(shuliang) as shuliang from tb group by id,name order by sum(shuliang) desc
--> (让你望见影子的墙)生成测试数据,时间:2008-12-04
if not object_id('tb') is null drop table tb Go Create table tb([id] int,[name] nvarchar(1),[shuliang] int) Insert tb select 1,N'a',1 union all select 2,N'b',1 union all select 3,N'c',2 union all select 4,N'd',4 union all select 1,N'a',1 union all select 3,N'c',3 union all select 4,N'd',1 union all select 3,N'c',2 Go Select * from tb select id,name,shuliang=sum(shuliang) from tb group by id,name order by sum(shuliang) desc3 c 7 4 d 5 1 a 2 2 b 1
我要是只要汇总后shuliang前十的怎么办啊??
select top 10 id, name, sum(shuliang) as shuliang from tb group by id,name order by sum(shuliang) desc
select TOP 10 id,name,sum(shuliang) as shuliang from ta group by id,name order by 3 desc
select
id,
name,
sum(shuliang) as shuliang
from table
group by id,name
order by shuliang desc
insert into tb select 1,'a',1
insert into tb select 2,'b',1
insert into tb select 3,'c',2
insert into tb select 4,'d',4
insert into tb select 1,'a',1
insert into tb select 3,'c',3
insert into tb select 4,'d',1
insert into tb select 3,'c',2
go
select id,name,sum(shuliang) as shuliang from tb group by id,name order by sum(shuliang) desc
go
drop table tb
/*
id name shuliang
----------- ---------- -----------
3 c 7
4 d 5
1 a 2
2 b 1(4 行受影响)
*/
id,
name,
sum(shuliang) as shuliang
from tb
order by sum(shuliang) desc
from ta
group by id,name
order by 3 desc
id,
name,
sum(shuliang) as shuliang
from tb
group by id,name
order by sum(shuliang) desc
--> (让你望见影子的墙)生成测试数据,时间:2008-12-04
if not object_id('tb') is null
drop table tb
Go
Create table tb([id] int,[name] nvarchar(1),[shuliang] int)
Insert tb
select 1,N'a',1 union all
select 2,N'b',1 union all
select 3,N'c',2 union all
select 4,N'd',4 union all
select 1,N'a',1 union all
select 3,N'c',3 union all
select 4,N'd',1 union all
select 3,N'c',2
Go
Select * from tb
select id,name,shuliang=sum(shuliang)
from tb
group by id,name
order by sum(shuliang) desc3 c 7
4 d 5
1 a 2
2 b 1
select top 10
id,
name,
sum(shuliang) as shuliang
from tb
group by id,name
order by sum(shuliang) desc
from ta
group by id,name
order by 3 desc