按性别分别排名,成绩相同则并列:update t set 排名=isnull((select count(1) from 表 where sex=t.sex and 成绩>t.成绩),0)+1 from 表 t
select name, sex, 成绩, 排名=(select count(1) from tb where name=t.name and sex=t.sex and 成绩>=t.成绩) from tb t
select name,sex,成绩,排名=row_number() over(partition sex order by 成绩) from tb
update a set 排名 = t.r from tb a,(select name,rank() over(partition by sex order by 成绩 desc) as r from tb) as t where a.name=t.name
这些操作不一定要用一个sql文搞定。 我目前想到的是色select name from T order by 成绩 desc group by sex.这样就把男女生成绩分开,并按从高到低排序了。但如何更新回表中呢?
select name,sex,成绩,row_number() over(partition by sex,order by 成绩) as 排名 from tb
declare @tb table (name varchar(10),sex char(2),成绩 int,排名 int) insert @tb values('jgj','男',598,null) insert @tb values('abc','男',523,null) insert @tb values('def','女',588,null) insert @tb values('ggg','女',515,null) select name,sex,成绩,排名=row_number() over(partition by sex order by 成绩 desc) from @tb /* name sex 成绩 排名 ---------- ---- ----------- -------------------- jgj 男 598 1 abc 男 523 2 def 女 588 1 ggg 女 515 2 */
update tb set 排名 = (select 排名 from tb c where name = c.name )
select name , RANK()OVER(ORDER BY chengji) AS 名次, from T group by sex
你可以使用MSSQL2005提供的排名函数!SELECT Name,SEX,成绩,ROW_NUMBER() OVER (ORDER BY 成绩) as 排名 FROM 表
update tb set 排名= (select 排名from (select name,sex,成绩,row_number() over(partition by sex,order by 成绩) as 排名 from tb )as c cwhere name= c.name )
select name,sex,成绩,排名,identity(int,1,1) '排名2' into #tt from table_name order by 成绩update table_name set table_name排名=#tt.排名2 from table_name ,#tt where table_name.name=#tt.name
--生成测试数据 create table #pm(sname varchar(50),sex varchar(50),cj int,pm int)insert into #pm(sname,sex,cj) select '李三','男',100 union select '张武','男',98 union select '王四','男',87 union select '刘二','男',87 union select '李二','女',99 union select '张四','女',98 union select '王三','女',100 union select '刘无','女',80 --实现按性别,分数排名 select sname,sex,cj ,(isnull((select count(1) from #pm where sex=t.sex and cj>t.cj),0)+1) As pm1 from #pm t order by sex,cj desc /* 结果 sname sex cj pm1 -------------------------------------------------- -------------------------------------------------- ----------- ----------- 李三 男 100 1 张武 男 98 2 王四 男 87 3 刘二 男 87 3 王三 女 100 1 李二 女 99 2 张四 女 98 3 刘无 女 80 4 */-- 更新 update #pm set pm = (isnull((select count(1) from #pm where sex=t.sex and cj>t.cj),0)+1) from #pm t-- 查询更新结果 select * from #pm /* sname sex cj pm -------------------------------------------------- -------------------------------------------------- ----------- ----------- 李二 女 99 2 李三 男 100 1 刘二 男 87 3 刘无 女 80 4 王三 女 100 1 王四 男 87 3 张四 女 98 3 张武 男 98 2 */ -- 排序后的结果 select * from #pm order by sex,pm /* sname sex cj pm -------------------------------------------------- -------------------------------------------------- ----------- ----------- 李三 男 100 1 张武 男 98 2 王四 男 87 3 刘二 男 87 3 王三 女 100 1 李二 女 99 2 张四 女 98 3 刘无 女 80 4 */
create table T(sname varchar(50),sex varchar(50),cj int,pm int)insert into T(sname,sex,cj) select '李三','男',100 union select '张武','男',98 union select '王四','男',87 union select '刘二','男',87 union select '李二','女',99 union select '张四','女',98 union select '王三','女',100 union select '刘无','女',80 select sname , sex,RANK()OVER(ORDER BY cj desc ) AS 名次,cj from T where sex='男' union all select sname , sex,RANK()OVER(ORDER BY cj desc ) AS 名次,cj from T where sex='女'/* sname sex 名次 cj -------------------------------------------------- -------------------------------------------------- -------------------- ----------- 李三 男 1 100 张武 男 2 98 刘二 男 3 87 王四 男 3 87 王三 女 1 100 李二 女 2 99 张四 女 3 98 刘无 女 4 80(8 行受影响) */
create table bzwm(name varchar(10),sex char(2),成绩 int,排名 int) delete bzwm insert bzwm values('jgj','男',598,null) insert bzwm values('abc','男',523,null) insert bzwm values('abc','男',523,null) insert bzwm values('def','女',588,null) insert bzwm values('ggg','女',515,null) select name,sex,成绩,排名=isnull((select count(1) from bzwm where sex=t.sex and 成绩>t.成绩),0)+1 from bzwm t
很感谢大家,但是下面这个版本:select name,sex,成绩,排名=isnull((select count(1) from bzwm where sex=t.sex and 成绩>t.成绩),0)+1 from bzwm t 我用的mysql,提示出错。看来我得恶补一下SQL了惭愧!
create table T(sname varchar(50),sex varchar(50),cj int,pm int)insert into T(sname,sex,cj) select '李三','男',100 union select '张武','男',98 union select '王四','男',87 union select '刘二','男',87 union select '李二','女',99 union select '张四','女',98 union select '王三','女',100 union select '刘无','女',80select * from Tif object_id('#t') is not null drop table #t create table #t ( sname varchar(50), sex varchar(4), cj int, pm int identity(1,1) )insert into #t select sname,sex,cj from T order by cj descselect * from #t where sex = '男' union all select * from #t where sex = '女'/** 李三 男 100 1 张武 男 98 5 王四 男 87 6 刘二 男 87 7 王三 女 100 2 李二 女 99 3 张四 女 98 4 刘无 女 80 8 (所影响的行数为 8 行) **/
select sname,sex,cj,排名=isnull((select count(1) from T where sex=t.sex and cj>t.cj),0)+1 from T t/***sname sex cj 排名 -------------------------------------------------- -------------------------------------------------- ----------- ----------- 李二 女 99 1 李三 男 100 1 刘二 男 87 1 刘无 女 80 1 王三 女 100 1 王四 男 87 1 张四 女 98 1 张武 男 98 1(所影响的行数为 8 行) **/
mysql is null mssql isnull
declare @tb table (name varchar(10),sex NVARCHAR(1),score int,[Rank] int) insert @tb values('jgj',N'男',598,null) insert @tb values('abc',N'男',523,null) insert @tb values('abc',N'男',523,null) insert @tb values('abc',N'男',521,null) insert @tb values('def',N'女',588,null) insert @tb values('ggg',N'女',515,null)UPDATE @tb SET [Rank]=(SELECT COUNT(1) FROM @tb WHERE score>T1.score AND T1.sex=sex)+1 FROM @tb T1SELECT * FROM @tb
create table lintest(名字 varchar(10),性别 varchar(2),成绩 decimal(4,2),排名 int) insert into lintest select 'a','男',56,'' insert into lintest select 'b','男',78,'' insert into lintest select 'c','女',82,'' insert into lintest select 'd','男',45,'' insert into lintest select 'e','女',78,'' insert into lintest select 'f','男',68,'' insert into lintest select 'g','男',48,'' insert into lintest select 'h','女',38,'' insert into lintest select 'i','女',98,'' update lintest set 排名=(select count(distinct 名字) from lintest where 成绩>=a.成绩 and 性别=a.性别) from lintest a select * from lintest order by 性别,排名 go drop table lintest
create table lintest(名字 varchar(10),性别 varchar(2),成绩 decimal(4,2),排名 int) insert into lintest select 'a','男',56,'' insert into lintest select 'b','男',78,'' insert into lintest select 'c','女',82,'' insert into lintest select 'd','男',45,'' insert into lintest select 'e','女',78,'' insert into lintest select 'f','男',68,'' insert into lintest select 'g','男',48,'' insert into lintest select 'h','女',38,'' insert into lintest select 'i','女',98,'' update lintest set 排名=(select count(distinct 成绩) from lintest where 成绩>=a.成绩 and 性别=a.性别) from lintest a select * from lintest order by 性别,排名 go drop table lintest这是第2种
set
排名=isnull((select count(1) from 表 where sex=t.sex and 成绩>t.成绩),0)+1
from
表 t
name,
sex,
成绩,
排名=(select count(1) from tb where name=t.name and sex=t.sex and 成绩>=t.成绩)
from
tb t
排名 = t.r
from tb a,(select name,rank() over(partition by sex order by 成绩 desc) as r
from tb) as t
where a.name=t.name
我目前想到的是色select name from T order by 成绩 desc group by sex.这样就把男女生成绩分开,并按从高到低排序了。但如何更新回表中呢?
select name,sex,成绩,row_number() over(partition by sex,order by 成绩) as 排名
from tb
insert @tb values('jgj','男',598,null)
insert @tb values('abc','男',523,null)
insert @tb values('def','女',588,null)
insert @tb values('ggg','女',515,null)
select name,sex,成绩,排名=row_number() over(partition by sex order by 成绩 desc) from @tb
/*
name sex 成绩 排名
---------- ---- ----------- --------------------
jgj 男 598 1
abc 男 523 2
def 女 588 1
ggg 女 515 2
*/
update tb set 排名 = (select 排名 from tb c where name = c.name )
from T
group by sex
update tb set 排名= (select 排名from (select name,sex,成绩,row_number() over(partition by sex,order by 成绩) as 排名
from tb
)as c cwhere name= c.name )
into #tt
from table_name order by 成绩update table_name set table_name排名=#tt.排名2
from table_name ,#tt
where table_name.name=#tt.name
--生成测试数据
create table #pm(sname varchar(50),sex varchar(50),cj int,pm int)insert into #pm(sname,sex,cj)
select '李三','男',100
union
select '张武','男',98
union
select '王四','男',87
union
select '刘二','男',87
union
select '李二','女',99
union
select '张四','女',98
union
select '王三','女',100
union
select '刘无','女',80
--实现按性别,分数排名
select sname,sex,cj ,(isnull((select count(1) from #pm where sex=t.sex and cj>t.cj),0)+1) As pm1
from #pm t
order by sex,cj desc
/* 结果
sname sex cj pm1
-------------------------------------------------- -------------------------------------------------- ----------- -----------
李三 男 100 1
张武 男 98 2
王四 男 87 3
刘二 男 87 3
王三 女 100 1
李二 女 99 2
张四 女 98 3
刘无 女 80 4
*/-- 更新
update #pm set pm = (isnull((select count(1) from #pm where sex=t.sex and cj>t.cj),0)+1)
from #pm t-- 查询更新结果
select *
from #pm
/*
sname sex cj pm
-------------------------------------------------- -------------------------------------------------- ----------- -----------
李二 女 99 2
李三 男 100 1
刘二 男 87 3
刘无 女 80 4
王三 女 100 1
王四 男 87 3
张四 女 98 3
张武 男 98 2
*/
-- 排序后的结果
select *
from #pm
order by sex,pm
/*
sname sex cj pm
-------------------------------------------------- -------------------------------------------------- ----------- -----------
李三 男 100 1
张武 男 98 2
王四 男 87 3
刘二 男 87 3
王三 女 100 1
李二 女 99 2
张四 女 98 3
刘无 女 80 4
*/
select '李三','男',100
union
select '张武','男',98
union
select '王四','男',87
union
select '刘二','男',87
union
select '李二','女',99
union
select '张四','女',98
union
select '王三','女',100
union
select '刘无','女',80
select sname , sex,RANK()OVER(ORDER BY cj desc ) AS 名次,cj
from T
where sex='男'
union all
select sname , sex,RANK()OVER(ORDER BY cj desc ) AS 名次,cj
from T
where sex='女'/*
sname sex 名次 cj
-------------------------------------------------- -------------------------------------------------- -------------------- -----------
李三 男 1 100
张武 男 2 98
刘二 男 3 87
王四 男 3 87
王三 女 1 100
李二 女 2 99
张四 女 3 98
刘无 女 4 80(8 行受影响)
*/
create table bzwm(name varchar(10),sex char(2),成绩 int,排名 int)
delete bzwm
insert bzwm values('jgj','男',598,null)
insert bzwm values('abc','男',523,null)
insert bzwm values('abc','男',523,null)
insert bzwm values('def','女',588,null)
insert bzwm values('ggg','女',515,null)
select name,sex,成绩,排名=isnull((select count(1) from bzwm where sex=t.sex and 成绩>t.成绩),0)+1
from bzwm t
from bzwm t
我用的mysql,提示出错。看来我得恶补一下SQL了惭愧!
create table T(sname varchar(50),sex varchar(50),cj int,pm int)insert into T(sname,sex,cj)
select '李三','男',100
union
select '张武','男',98
union
select '王四','男',87
union
select '刘二','男',87
union
select '李二','女',99
union
select '张四','女',98
union
select '王三','女',100
union
select '刘无','女',80select * from Tif object_id('#t') is not null
drop table #t
create table #t
(
sname varchar(50),
sex varchar(4),
cj int,
pm int identity(1,1)
)insert into #t select sname,sex,cj from T order by cj descselect * from #t where sex = '男'
union all
select * from #t where sex = '女'/**
李三
男 100 1 张武
男 98 5 王四
男 87 6 刘二
男 87 7 王三
女 100 2 李二
女 99 3 张四
女 98 4 刘无
女 80 8 (所影响的行数为 8 行)
**/
这里是SQL Server区,所以大家的解决方案都是基于SQL Server的。
既然实际环境是MySQL,语法上跟SQL Server有些区别,建议将这个问题转去MySQL。
select sname,sex,cj,排名=isnull((select count(1) from T where sex=t.sex and cj>t.cj),0)+1
from T t/***sname sex cj 排名
-------------------------------------------------- -------------------------------------------------- ----------- -----------
李二 女 99 1
李三 男 100 1
刘二 男 87 1
刘无 女 80 1
王三 女 100 1
王四 男 87 1
张四 女 98 1
张武 男 98 1(所影响的行数为 8 行)
**/
mssql isnull
insert @tb values('jgj',N'男',598,null)
insert @tb values('abc',N'男',523,null)
insert @tb values('abc',N'男',523,null)
insert @tb values('abc',N'男',521,null)
insert @tb values('def',N'女',588,null)
insert @tb values('ggg',N'女',515,null)UPDATE @tb
SET [Rank]=(SELECT COUNT(1) FROM @tb WHERE score>T1.score AND T1.sex=sex)+1
FROM @tb T1SELECT * FROM @tb
insert into lintest select 'a','男',56,''
insert into lintest select 'b','男',78,''
insert into lintest select 'c','女',82,''
insert into lintest select 'd','男',45,''
insert into lintest select 'e','女',78,''
insert into lintest select 'f','男',68,''
insert into lintest select 'g','男',48,''
insert into lintest select 'h','女',38,''
insert into lintest select 'i','女',98,''
update lintest set 排名=(select count(distinct 名字) from lintest where 成绩>=a.成绩 and 性别=a.性别) from lintest a
select * from lintest order by 性别,排名
go
drop table lintest
insert into lintest select 'a','男',56,''
insert into lintest select 'b','男',78,''
insert into lintest select 'c','女',82,''
insert into lintest select 'd','男',45,''
insert into lintest select 'e','女',78,''
insert into lintest select 'f','男',68,''
insert into lintest select 'g','男',48,''
insert into lintest select 'h','女',38,''
insert into lintest select 'i','女',98,''
update lintest set 排名=(select count(distinct 成绩) from lintest where 成绩>=a.成绩 and 性别=a.性别) from lintest a
select * from lintest order by 性别,排名
go
drop table lintest这是第2种