No 速度 值
1 171 1000
2 151 999
3 161 998
4 162 997
5 163 1000
6 175 999
7 159 1000
速度都是三位smallint，怎么查出速度后两位在70～79之间的最大值？
也就是No=6的那一条。
------解决方案--------------------------------------------------------
select * from table2 where cast(SUBSTR(char(speed),2,2) as integer) in
(select max(cast(SUBSTR(char(speed),2,2) as integer)) as t from table2 where cast(SUBSTR(char(speed),2,2) as integer) > 70 and cast(SUBSTR(char(speed),2,2) as integer) <79)
db2是很麻烦,上面的能得到你要的.
------解决方案--------------------------------------------------------
楼上忘记要写 <=79咯
另外个人觉得使用 (speed-speed/100*100) 要比CAST好点吧:)
------解决方案--------------------------------------------------------
select * from tablename order by mod(speed,100) desc fetch first rows only
------解决方案--------------------------------------------------------
select max(mod(速度,100)) from table where mod(速度,100) between 70 and 79