表结构如下:
ID XSNAME XK CJ
1 张三 语文 100
2 张三 数学 99
3 李四 语文 70
4 李四 数学 85
5 王五 语文 85
6 赵六 数学 80
7 王五 数学 70
8 赵六 语文 99
9 某人 语文 70
10 某人 数学 65要得到学生的总分排序 如下:xsname zf mc
张三 199 1
赵六 179 2
李四 155 3
王五 155 3
某人 135 5该怎么写SQL呀?
ID XSNAME XK CJ
1 张三 语文 100
2 张三 数学 99
3 李四 语文 70
4 李四 数学 85
5 王五 语文 85
6 赵六 数学 80
7 王五 数学 70
8 赵六 语文 99
9 某人 语文 70
10 某人 数学 65要得到学生的总分排序 如下:xsname zf mc
张三 199 1
赵六 179 2
李四 155 3
王五 155 3
某人 135 5该怎么写SQL呀?
from table
order by zf desc
group by xsname
Insert into @t
select 1 ,'张三','语文', 100
union select 2 ,'张三','数学', 99
union select 3 ,'李四','语文', 70
union select 4 ,'李四','数学', 85
union select 5 ,'王五','语文', 85
union select 6 ,'赵六','数学', 80
union select 7 ,'王五','数学', 70
union select 8 ,'赵六','语文', 99
union select 9 ,'某人','语文', 70
union select 10 ,'某人','数学', 65select XSNAME,zf=sum(CJ),mc=(
Select count(*) from (select XSNAME,zf=sum(CJ) from @t group by XSName) t
where t.zf>sum(a.cj) )+1
from @t as a group by XSNAME order by 3
(
ID int,
XSNAME varchar(20),
XK varchar(20),
CJ int
)
insert test
select 1 ,'张三','语文', 100 union all
select 2 ,'张三','数学', 99 union all
select 3 ,'李四','语文', 70 union all
select 4 ,'李四','数学', 85 union all
select 5 ,'王五','语文', 85 union all
select 6 ,'赵六','数学', 80 union all
select 7 ,'王五','数学', 70 union all
select 8 ,'赵六','语文', 99 union all
select 9 ,'某人','语文', 70 union all
select 10 ,'某人','数学', 65select * from testselect XSNAME,
sum(CJ) zf,
mc=( select count(*)+1 from (select XSNAME,zf=sum(CJ) from test group by XSName) t
where t.zf>sum(a.CJ))
from test a group by XSNAME order by mc
(select count(distinct XSNAME)
from (
select XSNAME , sum(CJ) as zf
from tablename a
group by xsname
) as t
where zf>=sum(CJ)) as mc
from tablename a
group by xsname
order by sum(CJ) desc
(
ID int,
XSNAME varchar(20),
XK varchar(20),
CJ int
)
insert @test
select 1 ,'张三','语文', 100 union all
select 2 ,'张三','数学', 99 union all
select 3 ,'李四','语文', 70 union all
select 4 ,'李四','数学', 85 union all
select 5 ,'王五','语文', 85 union all
select 6 ,'赵六','数学', 80 union all
select 7 ,'王五','数学', 70 union all
select 8 ,'赵六','语文', 99 union all
select 9 ,'某人','语文', 70 union all
select 10 ,'某人','数学', 65
select XSNAME , sum(CJ) as zf ,
(select 1+count(distinct XSNAME)
from (
select XSNAME , sum(CJ) as zf
from @test a
group by xsname
) as t
where zf>sum(CJ)) as mc
from @test a
group by xsname
order by sum(CJ) desc--结果
XSNAME zf mc
-------------------- ----------- -----------
张三 199 1
赵六 179 2
王五 155 3
李四 155 3
某人 135 5(所影响的行数为 5 行)
from table
order by zf desc
group by xsname