T_Method表
MethodId MethodPrice
1 100
2 10
3 1T_Record表
RecordId MethodId SeasonId UserPhone
1 1 1 13000000000
2 1 1 13000000001
3 2 1 13000000001
4 1 1 13000000002
5 2 1 13000000002
6 1 1 13000000003
7 2 1 13000000003
8 3 1 13000000003
9 1 1 13000000004
10 2 1 13000000004T_User表
UserId UserPhone UserName
1 13000000000 张三
2 13000000001 李四
3 13000000002 冯五
4 13000000003 谢六
5 13000000004 周七想得出的排名结果:
1 谢六 13000000003 (4600-111) 从低到高排列
2 李四 13000000001 (4600-110) (相同价钱,先登记的排前面)
3 冯五 13000000002 (4600-110)
4 周七 13000000004 (4600-110)
5 张三 13000000000 (4600-100)select Row_Number() over(order by SavePrice asc) as RN,T_User.UserName,T_User.UserPhone,t.SavePrice from T_User inner join
(select UserSelf,4600-SUM(MethodPrice) as SavePrice from T_Record inner join T_Method on T_Record.MethodId=T_Method.MethodId
where SeasonId=1
group by UserPhone)t on T_User.UserPhone=t.UserPhone order by SavePrice asc得出的结果却是
1 谢六 13000000003 (4600-111) 从低到高排列
2 冯五 13000000002 (4600-110) (按中文名(A-Z)排序了)
3 李四 13000000001 (4600-110)
4 周七 13000000004 (4600-110)
5 张三 13000000000 (4600-100)求解决办法。。
MethodId MethodPrice
1 100
2 10
3 1T_Record表
RecordId MethodId SeasonId UserPhone
1 1 1 13000000000
2 1 1 13000000001
3 2 1 13000000001
4 1 1 13000000002
5 2 1 13000000002
6 1 1 13000000003
7 2 1 13000000003
8 3 1 13000000003
9 1 1 13000000004
10 2 1 13000000004T_User表
UserId UserPhone UserName
1 13000000000 张三
2 13000000001 李四
3 13000000002 冯五
4 13000000003 谢六
5 13000000004 周七想得出的排名结果:
1 谢六 13000000003 (4600-111) 从低到高排列
2 李四 13000000001 (4600-110) (相同价钱,先登记的排前面)
3 冯五 13000000002 (4600-110)
4 周七 13000000004 (4600-110)
5 张三 13000000000 (4600-100)select Row_Number() over(order by SavePrice asc) as RN,T_User.UserName,T_User.UserPhone,t.SavePrice from T_User inner join
(select UserSelf,4600-SUM(MethodPrice) as SavePrice from T_Record inner join T_Method on T_Record.MethodId=T_Method.MethodId
where SeasonId=1
group by UserPhone)t on T_User.UserPhone=t.UserPhone order by SavePrice asc得出的结果却是
1 谢六 13000000003 (4600-111) 从低到高排列
2 冯五 13000000002 (4600-110) (按中文名(A-Z)排序了)
3 李四 13000000001 (4600-110)
4 周七 13000000004 (4600-110)
5 张三 13000000000 (4600-100)求解决办法。。
按userid排序
不是按userid排的,是按在T_Record的先后顺序
(4600-111)
按照这个从低到高排列如果SavePrice相同,则按
RecordId MethodId SeasonId UserPhone
1 1 1 13000000000
2 1 1 13000000001
4 1 1 13000000002
6 1 1 13000000003
9 1 1 13000000004次序排前面的在前