语句如下:
SELECT xuehao,xingming,yuwen,
(SELECT COUNT(*)+1 FROM he_chengji WHERE a.yuwen<yuwen) AS 名次
FROM he_chengji AS a where xuehao='15710' order by 名次
当yuwen成绩整数部分是3位数以上的时候排名次就乱了,yuwen字段类型是varchar、长度30
看看你的sql语句和成绩字段类型。
SELECT xuehao,xingming,yuwen,
(SELECT COUNT(*)+1 FROM he_chengji WHERE CAST(a.yuwen AS SIGNED)<CAST(yuwen AS SIGNED)) AS 名次
FROM he_chengji AS a where xuehao='15710' order by 名次;
上面的方法没问题,加一个类型转换,字符串排序方式和整形方式不一样的!
追问这样小数还是有问题的
哈哈 没想到还有小数点。 把这个类型改一下成浮点型:
“CAST(a.yuwen AS SIGNED)<CAST(yuwen AS SIGNED)) ” 改成 CAST(a.yuwen AS DECIMAL)<CAST(yuwen AS DECIMAL))
完整语句:
SELECT xuehao,xingming,yuwen,
(SELECT COUNT(*)+1 FROM he_chengji WHERE CAST(a.yuwen AS SIGNED)<CAST(yuwen AS SIGNED)) AS 名次
FROM he_chengji AS a where xuehao='15710' order by 名次;
如果没问题,求采纳哈
追问不行的,还是有问题用DECIMAL