34,587
社区成员
发帖
与我相关
我的任务
分享
name 总分
---- ---
李三 143
黄五 125
张三 101
select name,sum(score) as [总分] from t1
group by name
order by sum(score) desc
if object_id('tb','U') is not null
drop table tb
go
create table tb
(
name varchar(10),
score int
)
go
insert into tb
select '张三',23 union all
select '张三',78 union all
select '黄五',57 union all
select '黄五',68 union all
select '李三',65 union all
select '李三',78
go
select name,qty=sum(score) over(partition by name) from tb order by qty desc
go
/*
name qty
---------- -----------
李三 143
李三 143
黄五 125
黄五 125
张三 101
张三 101
(6 行受影响)
*/
create table tb
(
name varchar(10),
score int
)
go
insert into tb
select '张三',23 union all
select '张三',78 union all
select '黄五',57 union all
select '黄五',68 union all
select '李三',65 union all
select '李三',78
go
select a.*,b.score as sumScore
from tb a,(select name,sum(score) score from tb group by name) b
where a.name = b.name
order by b.score desc,a.name,a.score desc
drop table tb
/**************
name score sumScore
---------- ----------- -----------
李三 78 143
李三 65 143
黄五 68 125
黄五 57 125
张三 78 101
张三 23 101
(6 行受影响)
if object_id('tb','U') is not null
drop table tb
go
create table tb
(
name varchar(10),
score int
)
go
insert into tb
select '张三',23 union all
select '张三',78 union all
select '黄五',57 union all
select '黄五',68 union all
select '李三',65 union all
select '李三',78
go
select name,sum(score) from tb group by name order by sum(score) desc
go
/*
name
---------- -----------
李三 143
黄五 125
张三 101
(3 行受影响)
*/
select a.*
from tb a,(select name,sum(score) score from tb group by name) b
where a.name = b.name
order by b.score desc,a.name,a.score desc
select name,sum(score) from t group by name order by 2
select t.name , (select sum(score) from tb where name = t.name) score from tb order by score desc
select t.name , (select sum(score) from tb where name = t.name) score from tb order by score desc
create table tb(name varchar(10),score int)
insert into tb select '张三',23
insert into tb select '张三',78
insert into tb select '黄五',57
insert into tb select '黄五',68
insert into tb select '李三',65
insert into tb select '李三',78
go
--这样?
select * from tb a order by (select sum(score) from tb where name=a.name) desc
/*
name score
---------- -----------
李三 65
李三 78
黄五 57
黄五 68
张三 23
张三 78
(6 行受影响)
*/
--还是:
select name,(select sum(score) from tb where name=a.name)score from tb a order by 2 desc
/*
name score
---------- -----------
李三 143
李三 143
黄五 125
黄五 125
张三 101
张三 101
(6 行受影响)
*/
go
drop table tb