有2张表 VIP 表 ID uname(公司名称) VIP1表 ID cid jifen
现在有如下数据:VIP表: 1,ABC
2,def
vip1表: 1,1,50
2,1,50
3,2,20
4,2,222
现已:SELECT top 8 vip.uname,sum(vip1.jifen) as zongfen FROM vip INNER JOIN vip1 ON vip.id = vip1.cid group by vip.uname order by sum(vip1.jifen) desc查询出来名次情况就是如果总分相同的时候就不显示前八名了、问改如何解决?
现在有如下数据:VIP表: 1,ABC
2,def
vip1表: 1,1,50
2,1,50
3,2,20
4,2,222
现已:SELECT top 8 vip.uname,sum(vip1.jifen) as zongfen FROM vip INNER JOIN vip1 ON vip.id = vip1.cid group by vip.uname order by sum(vip1.jifen) desc查询出来名次情况就是如果总分相同的时候就不显示前八名了、问改如何解决?
name score
aa 99
bb 56
cc 56
dd 77
ee 78
ff 76
gg 78
ff 501. 名次生成方式1,Score重复时合并名次
SELECT * , Place=(SELECT COUNT(DISTINCT Score) FROM jh03 WHERE Score >= a.Score)
FROM jh03 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 62. 名次生成方式2 , Score重复时保留名次空缺
SELECT * , Place=(SELECT COUNT(Score) FROM jh03 WHERE Score > a.Score) + 1
FROM jh03 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