27,579
社区成员
发帖
与我相关
我的任务
分享
---2000
--示例数据
CREATE TABLE tb(Name varchar(10),Score decimal(10,2))
INSERT tb SELECT 'aa',99
UNION ALL SELECT 'bb',56
UNION ALL SELECT 'cc',56
UNION ALL SELECT 'dd',77
UNION ALL SELECT 'ee',78
UNION ALL SELECT 'ff',76
UNION ALL SELECT 'gg',78
UNION ALL SELECT 'ff',50
GO
--1. 名次生成方式1,Score重复时合并名次
SELECT *,Place=(SELECT COUNT(DISTINCT Score) FROM tb WHERE Score>=a.Score)
FROM tb a
ORDER BY Place
/*--结果
Name Score Place
---------------- ----------------- -----------
aa 99.00 1
ee 78.00 2
gg 78.00 2
dd 77.00 3
ff 76.00 4
bb 56.00 5
cc 56.00 5
ff 50.00 6
--*/
--2. 名次生成方式2,Score重复时保留名次空缺
SELECT *,Place=(SELECT COUNT(Score) FROM tb WHERE Score>a.Score)+1
FROM tb a
ORDER BY Place
/*--结果
Name Score Place
--------------- ----------------- -----------
aa 99.00 1
ee 78.00 2
gg 78.00 2
dd 77.00 4
ff 76.00 5
bb 56.00 6
cc 56.00 6
ff 50.00 8
--*/
--借用77的数据
if object_id('[TB]') is not null drop table [TB]
go
create table [TB]([A] varchar(2),[B] varchar(4))
insert [TB]
select '张','20分'union all
select '王','19分'union all
select '李','19分'union all
select '孙','17分'
select a,b, aa=dense_rank() over(order by b desc) from tb
/*
a b aa
---- ---- --------------------
张 20分 1
王 19分 2
李 19分 2
孙 17分 3
(4 行受影响)
*/
----------------------------------------------------------------
-- Author :SQL77(只为思齐老)
-- Date :2010-01-14 08:22:28
-- Version:
-- Microsoft SQL Server 2000 - 8.00.194 (Intel X86)
-- Aug 6 2000 00:57:48
-- Copyright (c) 1988-2000 Microsoft Corporation
-- Desktop Engine on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:[TB]
if object_id('[TB]') is not null drop table [TB]
go
create table [TB]([A] varchar(2),[B] varchar(4),[C] int)
insert [TB]
select '张','20分',1 union all
select '王','19分',2 union all
select '李','19分',2 union all
select '孙','17分',3
--------------开始查询--------------------------
select A,B,(SELECT COUNT(DISTINCT B) FROM TB WHERE B>=T.B)AS 排名 from [TB] T
----------------结果----------------------------
/*
(所影响的行数为 4 行)
A B 排名
---- ---- -----------
张 20分 1
王 19分 2
李 19分 2
孙 17分 3
(所影响的行数为 4 行)
*/
select
Inspect_name,office,IPfrom,
检查次数=sum(case when 数量>120 then 1 else 0 end),
A=sum(case when A>120 then 1 else 0 end),
B=sum(case when B>120 then 1 else 0 end),
双休日=sum(case when A>120 then 1 else 0 end),
总分=sum(case when 双休日>120 then 1 else 0 end)*1
+sum(case when A>120 then 1 else 0 end)*2 +sum(case when B>120 then 1 else 0 end)*1 INTO #TT
from
(select
Inspect_name,office,IPfrom,
convert(varchar(10),intime,120) as intime,sum(vtime) as 数量,
sum(case when rank= 'A' then vtime else 0 end) A,
sum(case when rank = 'B' then vtime else 0 end) B,
sum(case when datepart(dw,intime) = '1' or datepart(dw,intime) = '7' then vtime else 0 end) 双休日
from
jcxs
group by Inspect_name,office,IPfrom,convert(varchar(10),intime,120)
)t
group by Inspect_name,office,IPfrom order by 总分 desc
;with f as
(select Inspect_name,office,IPfrom,
检查次数=sum(case when 数量>120 then 1 else 0 end),
A=sum(case when A>120 then 1 else 0 end),
B=sum(case when B>120 then 1 else 0 end),
双休日=sum(case when A>120 then 1 else 0 end),
总分=sum(case when 双休日>120 then 1 else 0 end)*1 +sum(case when A>120 then 1 else 0 end)*2 +sum(case when B>120 then 1 else 0 end)*1
from (select Inspect_name,office,IPfrom,convert(varchar(10),intime,120) as intime,sum(vtime) as 数量,
sum(case when rank= 'A' then vtime else 0 end) A,
sum(case when rank= 'B' then vtime else 0 end) B,
sum(case when datepart(dw,intime) = '1' or datepart(dw,intime) = '7' then vtime else 0 end) 双休日
from jcxs group by Inspect_name,office,IPfrom,convert(varchar(10),intime,120) )t group by Inspect_name,office,IPfrom order by 总分 desc )
select A,B,(SELECT COUNT(DISTINCT B) FROM f WHERE B>=T.B)AS 排名 from f T