SELECT * FROM card where cast(substring(number,2) as unsigned) between 101010768 and 101010772
这个不可以,效果如下图: SELECT * FROM card where cast(substring(number,2) as unsigned) between '101010768' and '101010772';
你需要先弄清楚为什么排序有多余的结果因为你排序的字段是字符类型,所以排序的时候是按照字符进行排序如果只想得到Z开头的SELECT * FROM card where substring(number,1,1) = 'Z' and cast(substring(number,2) as unsigned) between '101010768' and '101010772';
突然想到了,这样就行了: SELECT * FROM card where length(number)=10 and number between 'Z101010768' and 'Z101010772';
101010768 and 101010772
这个不可以,效果如下图:
SELECT * FROM card where cast(substring(number,2) as unsigned) between '101010768' and '101010772';
substring(number,1,1) = 'Z' and
cast(substring(number,2) as unsigned) between '101010768' and '101010772';
SELECT * FROM card where length(number)=10 and number between 'Z101010768' and 'Z101010772';