MSSQL语句实现排名次
来源:互联网 发布:织梦cms视频教程下载 编辑:程序博客网 时间:2024/05/01 14:58
use pubs
go
select *,(select count(distinct min_lvl) from jobs a where a.min_lvl >= b.min_lvl ) mingci from jobs b order by min_lvl desc
结果:
/*
job_id job_desc min_lvl max_lvl mingci
------ -------------------------------------------------- ------- ------- -----------
2 Chief Executive Officer 200 250 1
3 Business Operations Manager 175 225 2
4 Chief Financial Officier 175 250 2
5 Publisher 150 250 3
6 Managing Editor 140 225 4
7 Marketing Manager 120 200 5
8 Public Relations Manager 100 175 6
9 Acquisitions Manager 75 175 7
10 Productions Manager 75 165 7
11 Operations Manager 75 150 7
12 Editor 25 100 8
13 Sales Representative 25 100 8
14 Designer 25 100 8
1 New Hire - Job not specified 10 10 9
*/
select *,排名=( select count(*) from ( select min_lvl from jobs) as a where min_lvl>b.min_lvl ) +1
from ( select * from jobs ) as b order by 排名
select *,(select count(min_lvl) from (select min_lvl from jobs) a where a.min_lvl>jobs.min_lvl)+1 as mincis from jobs order by mincis asc --不连续排名
select *,(select count(distinct min_lvl) from (select min_lvl from jobs) a where a.min_lvl>jobs.min_lvl)+1 as mincis from jobs order by mincis asc --连续排名
/*
job_id job_desc min_lvl max_lvl 排名
------ -------------------------------------------------- ------- ------- -----------
2 Chief Executive Officer 200 250 1
3 Business Operations Manager 175 225 2
4 Chief Financial Officier 175 250 2
5 Publisher 150 250 4
6 Managing Editor 140 225 5
7 Marketing Manager 120 200 6
8 Public Relations Manager 100 175 7
9 Acquisitions Manager 75 175 8
10 Productions Manager 75 165 8
11 Operations Manager 75 150 8
12 Editor 25 100 11
13 Sales Representative 25 100 11
14 Designer 25 100 11
1 New Hire - Job not specified 10 10 14
*/
create table tb(考号 varchar(10),科目代号 varchar(10),得分 int)
insert into tb select '0001' ,'01', 60
union all select '0001' ,'02', 80
union all select '0001' ,'03', 90
union all select '0001' ,'04', 100
union all select '0002' ,'01', 50
union all select '0002' ,'02', 40
union all select '0002' ,'03', 60
union all select '0002' ,'04', 90
declare @sql varchar(8000)
set @sql='select a.考号'
select @sql=@sql+',[科目'+cast(科目代号 as varchar)+']=sum(case a.科目代号 when '''+cast(科目代号 as varchar)+''' then a.得分 else 0 end)' from tb group by 科目代号
exec(@sql+',sum(a.得分) as 总分,identity(int,1,1) as 名次 into tmp from tb a group by a.考号 order by sum(a.得分) desc')
select * from tmp
drop table tb,tmp
- MSSQL语句实现排名次
- MSSQL语句实现排名次
- SQL 排名次的语句
- SQL语句实现排名
- 排名次
- 排名次
- mysql 实现 mssql exec 动态查询语句
- [MSSQL]汉字转拼音函数实现语句
- 模仿实现mssql的select语句
- MSSQL通过SQL语句实现发邮件
- Mssql语句
- Mssql语句
- Mssql语句
- MSSQL语句
- mysql 语句进行记录的排名实现
- 如何排名次
- 期末考试之排名次
- sql排名有名次
- CH17 委托 .net复习课
- 如何收集及删除列的统计信息
- iframe 标签 -- 代表HTML内联框架
- 文件系统Minifilter驱动(二)
- 亿万用户网站MySpace的成功秘密
- MSSQL语句实现排名次
- 2008囧言囧语大合集(来自公司内网)
- .htm和.html的区别
- 单元测试三叉戟—JUNIT,DBUNIT,UNITILS
- Mapinfo加载图片文件通用类
- 文件系统Minifilter驱动(三)
- JBPM学习笔记(一)
- 文件系统Minifilter驱动(四)
- vb软件破解手记