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
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 行受影响) */
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 goselect 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 descdrop table tb/**************name score sumScore ---------- ----------- ----------- 李三 78 143 李三 65 143 黄五 68 125 黄五 57 125 张三 78 101 张三 23 101(6 行受影响)
to :AcHeratfrom 多个表这是叫左连接还是叫什么?
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 行受影响) */
from 多个表叫多表联接 多表联接又分好几种 left join 左联接 right join 右联接 full join 全联接 inner join 内联接 cross join 交叉联接
select name,sum(score) from tb group by name order by sum(score) desc
那像AcHerat这种 from tb a,b 而没有写left join 或者right join 就一个 where a.id=b.id 之类的叫什么连接?,另外你写的over partition by 这个命令很好用,学习了
lz的意思很模糊啊,让人遐想菲菲,不过按照正常人的想法你是想要这样的结果吧 name 总分 ---- --- 李三 143 黄五 125 张三 101 select name,sum(score) as [总分] from t1 group by name order by sum(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
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
我也写一条,order by 后直接用数字表示按第几列排序select name,sum(score) from t group by name order by 2
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
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 行受影响)
*/
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
goselect 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 descdrop 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,qty=sum(score) over(partition by name) from tb order by qty desc
go
/*
name qty
---------- -----------
李三 143
李三 143
黄五 125
黄五 125
张三 101
张三 101(6 行受影响)
*/
多表联接又分好几种
left join 左联接
right join 右联接
full join 全联接
inner join 内联接
cross join 交叉联接
name 总分
---- ---
李三 143
黄五 125
张三 101
select name,sum(score) as [总分] from t1
group by name
order by sum(score) desc
---------- -----------
李三 143
李三 143
黄五 125
黄五 125
张三 101
张三 101
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
select t.name , (select sum(score) from tb where name = t.name) score from tb order by score desc