现假设有这样一个表
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
请高手赐教!问题解决利马给分,谢谢
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
请高手赐教!问题解决利马给分,谢谢
qty第二大——>rank 为6
qty第三大——>rank 为4
qty第四大——>rank 为7
qty第五大——>rank 为3这是啥规则?
qty第二大——>rank 为6
qty第三大——>rank 为4
qty第四大——>rank 为7
qty第五大——>rank 为3
??
这里怎么来的?
qty最大——>rank 为5
qty第二大——>rank 为6
qty第三大——>rank 为4
qty第四大——>rank 为7
qty第五大——>rank 为3
--------------------
为后面的余数,就是25笔中后5笔
比如 id: 1--35 qty就依着id好了.
那rank是怎麼排的
1 6 1
2 7 2
3 8 3
4 9 4
5 10 5
6 11 6
7 12 7
8 13 8
9 14 9
10 15 10
--(余数,也就是25后的5笔数据)
1 1 3 --qty第五大 rank为3
2 2 7 --qty第四大 rank为7
3 3 4 --qty第三大 rank为4
4 4 6 --qty第二大 rank为6
5 5 5 --qty第一大 rank为5
1 6 1
2 7 2
3 8 3
4 9 4
5 10 5
6 11 6
7 12 7
8 13 8
9 14 9
10 15 10
--(余数,也就是15后的5笔数据)(15和25 是一样的道理,就是后面余数5的规则为下)
1 1 3 --qty第五大 rank为3
2 2 7 --qty第四大 rank为7
3 3 4 --qty第三大 rank为4
4 4 6 --qty第二大 rank为6
5 5 5 --qty第一大 rank为5
为什么 第一大 rank为 5 而不是为6或者为7或者为4?
为什么 第二大 rank为 6 而不是为5或者为7或者为4?你的规定?是规定就说是你的规定嘛,不说谁知道是你的规定啊。
那余6个啥规定?余7个啥规定?只能为余5?你不说谁知道只能为5啊:)
1,2,3,4,5,6,7,8,9,(qty)
1,9,2,8,3,7,4,6,5 (rank)
就是这么对应的
qty第一大 rank为5
qty第二大 rank为6
qty第三大 rank为4
qty第四大 rank为7
qty第五大 rank为3
.
.
.
.
要不這樣,我辛苦一下,如下數據,樓主把rank填上去看看..
id qty rank
------------------------
1 100
2 200
3 300
4 400
5 500
6 600
7 700
8 800
9 900
10 1000
11 1100
12 1200
13 1300
14 1400
15 1500
16 1600
17 1700
LZ没把问题说完,或者自己都没想清楚。
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)
from tablename a,(select *,isnull((select count(*) from tablename where qty>a.qty),0) as No,(select count(*) from tablename) as All) as b
where a.id=b.id
------------------------
1 100 1
2 200 2
3 300 3
4 400 4
5 500 5
6 600 6
7 700 7
8 800 8
9 900 9
10 1000 10
11 1100 2
12 1200 8
13 1300 3
14 1400 7
15 1500 4
16 1600 6
17 1700 5
--------------
手工大概是:
1,2,3,4,5,6,7,8,9,(qty)
1,9,2,8,3,7,4,6,5 (rank)
qty/rank
...
100/10
99/5
98/6
97/4
96/7
95/3
94/8
93/2
92/9
91/1
90/10
89/5
...
以次类推是么?楼主?
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.idselect * 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 行)
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.idselect * from @aid 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 行)
--如果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.idselect * from @a
order by qty desc,id descid 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 行)
需求分析高手呀,哈哈 ^_^
——————————
确实有这个问题存在,
i9988(冒牌j9988 V0.3) 兄弟能不能
declare @a table (
id int,qty int, rank int,otherQty int
)
如果qty 重复则按otherQty 排列
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, 100id 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
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.idselect * from @a
order by qty desc,id descid 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 行)
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.idselect * from @a
order by qty desc,otherqty desc,id descid 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 行)
3、如果不能被10整除的记录,比如25,则前20笔按第2点统计,后面5笔记录rank应该为若现在有23条记录的话,是不是前20条先计算,之后21-23条再自己计算呀?