求SQL语句，有点难度（百分）

[flyzq]

现假设有这样一个表
id qty rank
1 1
2 2
3 3
4 4
5 5
6 6
7 7
8 8
9 9
10 10
. .
. .
按十分法统计rank排名，
规则：
1、按qty由大到小排名，qty最大则rank为10分，排下来如果刚好10笔记录依次9、8、7……；
2、如果大于10笔记录，且刚好是能被10整除，比如20笔记录，则是qty最大的前两笔为10分，再是两笔依次9、8、7……；
3、如果不能被10整除的记录，比如25，则前20笔按第2点统计，后面5笔记录rank应该为
qty最大——>rank 为5
qty第二大——>rank 为6
qty第三大——>rank 为4
qty第四大——>rank 为7
qty第五大——>rank 为3
请高手赐教！问题解决利马给分，谢谢

[jinxin19831117]

不懂，不是有点运，是很运...

[i9988]

--修改了小错误，测试结果

declare @a table (
id int,qty int, rank int
)

insert @a (id,qty)
select
1, 100
union all select
2 , 200
union all select
3 , 300
union all select
4 , 400
union all select
5 ,500
union all select
6, 600
union all select
7 , 700
union all select
8 , 800
union all select
9 , 900
union all select
10 ,1000
union all select
11, 1100
union all select
12 , 1200
union all select
13 , 1300
union all select
14 ,1400
union all select
15, 1500
union all select
16 , 1600
union all select
17 , 1700


update a
set rank = case when No<[All]/10*10 then 10-No/([All]/10)
else 5+power(-1,((No % 10)+1 % 2))*(((No % 10)+1)/2)
end
from @a a,(select *,isnull((select count(*) from @a where qty>x.qty),0) as No,(select count(*) from @a) as [All] from @a x) as b
where a.id=b.id

select * from @a

--结果
id qty rank
----------- ----------- -----------
1 100 2
2 200 8
3 300 3
4 400 7
5 500 4
6 600 6
7 700 5
8 800 1
9 900 2
10 1000 3
11 1100 4
12 1200 5
13 1300 6
14 1400 7
15 1500 8
16 1600 9
17 1700 10

（所影响的行数为 17 行）

[jinxin19831117]

晕

[i9988]

declare @a table (
id int,qty int, rank int
)

insert @a (id,qty)
select
1, 100
union all select
2 , 200
union all select
3 , 300
union all select
4 , 400
union all select
5 ,500
union all select
6, 600
union all select
7 , 700
union all select
8 , 800
union all select
9 , 900
union all select
10 ,1000
union all select
11, 1100
union all select
12 , 1200
union all select
13 , 1300
union all select
14 ,1400
union all select
15, 1500
union all select
16 , 1600
union all select
17 , 1700
union all select
18 , 1800
union all select
19 , 1900
union all select
20 , 2000
union all select
21 , 2100
union all select
22 , 2200


update a
set rank = case when No<[All]/10*10 then 10-No/([All]/10)
else 5+power(-1,((No % 10)+1 % 2))*(((No % 10)+1)/2)
end
from @a a,(select *,isnull((select count(*) from @a where qty>x.qty),0) as No,(select count(*) from @a) as [All] from @a x) as b
where a.id=b.id

select * from @a

id qty rank
----------- ----------- -----------
1 100 6
2 200 5
3 300 1
4 400 1
5 500 2
6 600 2
7 700 3
8 800 3
9 900 4
10 1000 4
11 1100 5
12 1200 5
13 1300 6
14 1400 6
15 1500 7
16 1600 7
17 1700 8
18 1800 8
19 1900 9
20 2000 9
21 2100 10
22 2200 10

（所影响的行数为 22 行）

[i9988]

--在qty不重复的情况下合乎lz要求了吧？
--如果qty重复，用id判断就可以了，改动处在下面标出

declare @a table (
id int,qty int, rank int
)

insert @a (id,qty)
select
1, 100
union all select
2 , 200
union all select
3 , 100
union all select
4 , 400
union all select
5 ,100
union all select
6, 600
union all select
7 , 700
union all select
8 , 800
union all select
9 , 100
union all select
10 ,1000
union all select
11, 100
union all select
12 , 1200
union all select
13 , 1200
union all select
14 ,1400
union all select
15, 1200
union all select
16 , 1600
union all select
17 , 1200
union all select
18 , 800
union all select
19 , 900
union all select
20 , 2000
union all select
21 , 2000
union all select
22 , 2000


update a
set rank = case when No<[All]/10*10 then 10-No/([All]/10)
else 5+power(-1,((No % 10)+1 % 2))*(((No % 10)+1)/2)
end
from @a a,(select *,isnull((select count(*) from @a where qty>x.qty
or (qty=x.qty and id>x.id) --加这里
),0) as No,(select count(*) from @a) as [All] from @a x) as b
where a.id=b.id

select * from @a
order by qty desc,id desc

id qty rank
----------- ----------- -----------
22 2000 10
21 2000 10
20 2000 9
16 1600 9
14 1400 8
17 1200 8
15 1200 7
13 1200 7
12 1200 6
10 1000 6
19 900 5
18 800 5
8 800 4
7 700 4
6 600 3
4 400 3
2 200 2
11 100 2
9 100 1
5 100 1
3 100 5
1 100 6

（所影响的行数为 22 行）

[xyxfly]

i9988(冒牌j9988 V0.3) ( ) 信誉：70 Blog


需求分析高手呀，哈哈 ^_^

[flyzq]

回复人：you_99(沙丁鱼)

如果qty值重了怎么办？
——————————
确实有这个问题存在，
i9988(冒牌j9988 V0.3) 兄弟能不能
declare @a table (
id int,qty int, rank int，otherQty int
)
如果qty 重复则按otherQty 排列

[flyzq]

declare @a table (
id int,qty int,otherqty int, rank int
)

insert @a (id,qty,otherqty)
select
1, 100, 100
union all select
2 , 200, 100
union all select
3 , 300, 100
union all select
4 , 400, 100
union all select
5 ,500, 100
union all select
6, 600, 100
union all select
7 , 700, 100
union all select
8 , 800, 100
union all select
9 , 900, 100
union all select
10 ,1000, 100
union all select
11, 1100, 100
union all select
12 , 1200, 100
union all select
13 , 1300, 100
union all select
14 ,1400, 100
union all select
15, 1500, 100
union all select
16 , 1600, 100
union all select
17 , 1600, 110
union all select
18 , 1800, 100
union all select
19 , 1900, 100
union all select
20 , 2000, 100
union all select
21 , 2100, 100
union all select
22 , 2200, 100



id qty rank
----------- ----------- -----------
1 100 6
2 200 5
3 300 1
4 400 1
5 500 2
6 600 2
7 700 3
8 800 3
9 900 4
10 1000 4
11 1100 5
12 1200 5
13 1300 6
14 1400 6
15 1500 7
16 1600 7
17 1600 8 --qty相同则otherqty为大的排前面
18 1800 8
19 1900 9
20 2000 9
21 2100 10
22 2200 10

[i9988]

当然可以，不过如果还是不能保证，就再用id判断

declare @a table (
id int,qty int, rank int,
otherQty int
)

insert @a (id,qty,otherQty)
select
1, 100,100
union all select
2 , 200,200
union all select
3 , 100,300
union all select
4 , 400,400
union all select
5 ,100,400
union all select
6, 600,400
union all select
7 , 700,600
union all select
8 , 800,900
union all select
9 , 100,400
union all select
10 ,1000,1000
union all select
11, 100,900
union all select
12 , 1200,500
union all select
13 , 1200,100
union all select
14 ,1400,400
union all select
15, 1200,600
union all select
16 , 1600,500
union all select
17 , 1200,1400
union all select
18 , 800,500
union all select
19 , 900,700
union all select
20 , 2000,800
union all select
21 , 2000,400
union all select
22 , 2000,600


update a
set rank = case when No<[All]/10*10 then 10-No/([All]/10)
else 5+power(-1,((No % 10)+1 % 2))*(((No % 10)+1)/2)
end
from @a a,(select *,isnull((select count(*) from @a where qty>x.qty
or (qty=x.qty and otherqty=x.otherqty) --加这里
or (qty=x.qty and otherqty=x.otherqty and id>x.id) --加这里
),0) as No,(select count(*) from @a) as [All] from @a x) as b
where a.id=b.id

select * from @a
order by qty desc,id desc

id qty rank otherQty
----------- ----------- ----------- -----------
22 2000 10 600
21 2000 10 400
20 2000 10 800
16 1600 8 500
14 1400 8 400
17 1200 7 1400
15 1200 7 600
13 1200 7 100
12 1200 7 500
10 1000 5 1000
19 900 5 700
18 800 4 500
8 800 4 900
7 700 3 600
6 600 3 400
4 400 2 400
2 200 2 200
11 100 1 900
9 100 1 400
5 100 1 400
3 100 1 300
1 100 1 100

（所影响的行数为 22 行）

[i9988]

回复晚了，结贴了

不过有手误，改下

declare @a table (
id int,qty int, rank int,
otherQty int
)

insert @a (id,qty,otherQty)
select
1, 100,100
union all select
2 , 200,200
union all select
3 , 100,300
union all select
4 , 400,400
union all select
5 ,100,400
union all select
6, 600,400
union all select
7 , 700,600
union all select
8 , 800,900
union all select
9 , 100,400
union all select
10 ,1000,1000
union all select
11, 100,900
union all select
12 , 1200,500
union all select
13 , 1200,100
union all select
14 ,1400,400
union all select
15, 1200,600
union all select
16 , 1600,500
union all select
17 , 1200,1400
union all select
18 , 800,500
union all select
19 , 900,700
union all select
20 , 2000,800
union all select
21 , 2000,400
union all select
22 , 2000,600


update a
set rank = case when No<[All]/10*10 then 10-No/([All]/10)
else 5+power(-1,((No % 10)+1 % 2))*(((No % 10)+1)/2)
end
from @a a,(select *,isnull((select count(*) from @a where qty>x.qty
or (qty=x.qty and otherqty>x.otherqty) --加这里
or (qty=x.qty and otherqty=x.otherqty and id>x.id) --加这里
),0) as No,(select count(*) from @a) as [All] from @a x) as b
where a.id=b.id

select * from @a
order by qty desc,otherqty desc,id desc

id qty rank otherQty
----------- ----------- ----------- -----------
20 2000 10 800
22 2000 10 600
21 2000 9 400
16 1600 9 500
14 1400 8 400
17 1200 8 1400
15 1200 7 600
12 1200 7 500
13 1200 6 100
10 1000 6 1000
19 900 5 700
8 800 5 900
18 800 4 500
7 700 4 600
6 600 3 400
4 400 3 400
2 200 2 200
11 100 2 900
9 100 1 400
5 100 1 400
3 100 5 300
1 100 6 100

（所影响的行数为 22 行）

[zhang_yzy]

是不是楼主的描述有问题：
3、如果不能被10整除的记录，比如25，则前20笔按第2点统计，后面5笔记录rank应该为

若现在有23条记录的话，是不是前20条先计算，之后21-23条再自己计算呀？

[playwarcraft]

老實說, 我還是沒看懂...汗

[liuxingstar]

应该是个加密的数据

[ww3347]

qty最大——>rank 为5
qty第二大——>rank 为6
qty第三大——>rank 为4
qty第四大——>rank 为7
qty第五大——>rank 为3

这是啥规则？

[xyxfly]

qty最大——>rank 为5
qty第二大——>rank 为6
qty第三大——>rank 为4
qty第四大——>rank 为7
qty第五大——>rank 为3
??
这里怎么来的？

[flyzq]

比如25，则前20笔按第2点统计，后面5笔记录rank应该为
qty最大——>rank 为5
qty第二大——>rank 为6
qty第三大——>rank 为4
qty第四大——>rank 为7
qty第五大——>rank 为3
--------------------
为后面的余数，就是25笔中后5笔

[ww3347]

问你，为啥最大排第5，第2大排第6，第3大排第4？

[playwarcraft]

看不懂耶~~~

[playwarcraft]

樓主舉個例子看看
比如 id: 1--35 qty就依着id好了.
那rank是怎麼排的

[simonhehe]

^_^ :)
一看就晕