一张表T,里面的数据按字段num排序后十等分(假设分成十份),以num最多的往下排,依次给它记录分数为10分,9分、8分、。。、1分
现在我要求出里面的一条数据属于几分?比如下表中id=7属于几分?请写出sql,谢谢!!
表如下:
id name num
--------------------
1 你1 2
2 你2 4
3 你3 2
4 你4 4
5 你5 3
6 你6 6
7 你7 7
8 你8 9
9 你9 10
10 你10 1
11 你11 2
12 你12 2
. . .
. . .
. . .
现在我要求出里面的一条数据属于几分?比如下表中id=7属于几分?请写出sql,谢谢!!
表如下:
id name num
--------------------
1 你1 2
2 你2 4
3 你3 2
4 你4 4
5 你5 3
6 你6 6
7 你7 7
8 你8 9
9 你9 10
10 你10 1
11 你11 2
12 你12 2
. . .
. . .
. . .
-- Author: liangCK 小梁
-- Date : 2008-11-08 15:38:17
---------------------------------
--> 生成测试数据: @T
DECLARE @T TABLE (id INT,name VARCHAR(4),num INT)
INSERT INTO @T
SELECT 1,'你1',2 UNION ALL
SELECT 2,'你2',4 UNION ALL
SELECT 3,'你3',2 UNION ALL
SELECT 4,'你4',4 UNION ALL
SELECT 5,'你5',3 UNION ALL
SELECT 6,'你6',6 UNION ALL
SELECT 7,'你7',7 UNION ALL
SELECT 8,'你8',9 UNION ALL
SELECT 9,'你9',10 UNION ALL
SELECT 10,'你10',1 UNION ALL
SELECT 11,'你11',2 UNION ALL
SELECT 12,'你12',2--SQL查询如下:SELECT id,name,num,
CASE WHEN rnk>10 THEN 0
ELSE rnk
END AS 几等分
FROM
(
SELECT id,name,num,
rnk=ROW_NUMBER()
OVER(ORDER BY num DESC)
FROM @T
) AS t
WHERE id=7/*
id name num 几等分
----------- ---- ----------- --------------------
7 你7 7 3(1 行受影响)*/
select top 10 percent * from t order by t desc
select top 10 percent * from t where t not in(select top 10 percent * from t order by t desc) order by t desc
select top 10 percent * from t where t not in(select top 20 percent * from t order by t desc) order by t desc
select top 10 percent * from t where t not in(select top 30 percent * from t order by t desc) order by t desc
select top 10 percent * from t where t not in(select top 40 percent * from t order by t desc) order by t desc
select top 10 percent * from t where t not in(select top 50 percent * from t order by t desc) order by t desc
select top 10 percent * from t where t not in(select top 60 percent * from t order by t desc) order by t desc
select top 10 percent * from t where t not in(select top 70 percent * from t order by t desc) order by t desc
select top 10 percent * from t where t not in(select top 80 percent * from t order by t desc) order by t desc
select top 10 percent * from t where t not in(select top 90 percent * from t order by t desc) order by t desc
select count(id)as cou,num,identity(int,10,-1)as mi into ttt# from t group by num order by cou desc
select mi from ttt# where num in(select num from t where id=7)
CREATE TABLE tb(id INT,name VARCHAR(4),num INT)
INSERT INTO tb
SELECT 1,'你1',2 UNION ALL
SELECT 2,'你2',4 UNION ALL
SELECT 3,'你3',2 UNION ALL
SELECT 4,'你4',4 UNION ALL
SELECT 5,'你5',3 UNION ALL
SELECT 6,'你6',6 UNION ALL
SELECT 7,'你7',7 UNION ALL
SELECT 8,'你8',9 UNION ALL
SELECT 9,'你9',10 UNION ALL
SELECT 10,'你10',1 UNION ALL
SELECT 11,'你11',2 UNION ALL
SELECT 12,'你12',2
select 10-count(*)/10 from (
select top 100 percent id from tb where num>=(select num from tb where id=7) order by num desc,id
)T
go
drop table tb
/*
10
*/
INSERT INTO tb
SELECT 1,'你1',2 UNION ALL
SELECT 2,'你2',4 UNION ALL
SELECT 3,'你3',2 UNION ALL
SELECT 4,'你4',4 UNION ALL
SELECT 5,'你5',3 UNION ALL
SELECT 6,'你6',6 UNION ALL
SELECT 7,'你7',7 UNION ALL
SELECT 8,'你8',9 UNION ALL
SELECT 9,'你9',10 UNION ALL
SELECT 10,'你10',1 UNION ALL
SELECT 11,'你11',2 UNION ALL
SELECT 12,'你12',2
declare @id int
set @id=11 --查id为11时对应值
select 10-(count(*)-1)/10 from (
select top 100 percent id,num from tb
where num>=(select num from tb where id=@id)
and id not in(select id from tb where num=(select num from tb where id=@id) and id>@id)
order by num desc,id
)T
go
drop table tb
/*
10
*/
CREATE TABLE tb(id INT,name VARCHAR(4),num INT)
INSERT INTO tb
SELECT 1,'你1',2 UNION ALL
SELECT 2,'你2',4 UNION ALL
SELECT 3,'你3',2 UNION ALL
SELECT 4,'你4',4 UNION ALL
SELECT 5,'你5',3 UNION ALL
SELECT 6,'你6',6 UNION ALL
SELECT 7,'你7',7 UNION ALL
SELECT 8,'你8',9 UNION ALL
SELECT 9,'你9',10 UNION ALL
SELECT 10,'你10',1 UNION ALL
SELECT 11,'你11',2 UNION ALL
SELECT 12,'你12',2
declare @id int
set @id=11 --查id为11时对应值
select 10-(count(*)-1)/10 from (
select top 100 percent id from tb
where num>=(select num from tb where id=@id)
and id not in(select id from tb where num=(select num from tb where id=@id) and id>@id)
order by num desc
)T
go
drop table tb
/*
10
*/