sql语句，在线等！

[iriscole]

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的那一条。

[iriscole]

哎，db2会的人咋就那么少呢！

[lsj19830812]

select * from table1 where SUBSTR(速度,1,2) in
select max(SUBSTR(速度,1,2)) from table1

[yangxiao_jiang]

to nicole_lixp():
你说的对，用你的，就不需要调用db2的函数进行转化了。

[yangxiao_jiang]

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是很麻烦,上面的能得到你要的.

[nicole_lixp]

楼上忘记要写 <=79咯

另外个人觉得使用 (speed-speed/100*100) 要比CAST好点吧:)

[songfrh]

select * from tablename order by mod(speed,100) desc fetch first rows only

[FlyFreeBird]

select max(mod(速度,100)) from table where mod(速度,100) between 70 and 79