tb:
col1 col2
a001 aa
a003 bb
a05 cc
a0322 dd
a01 eecol1为主键...主键是由 "a" + 数字组成
我每次要找到最大的 值 如上我要找a0322
得到记录为:
col1 col2
a0322 eee
col1 col2
a001 aa
a003 bb
a05 cc
a0322 dd
a01 eecol1为主键...主键是由 "a" + 数字组成
我每次要找到最大的 值 如上我要找a0322
得到记录为:
col1 col2
a0322 eee
where not exists(select 1 from tb
where cast(replace(col1,'a','') as int)>cast(replace(a.col1,'a','') as int))
SELECT TOP 1 * FROM TB ORDER BY COL1 DESC
CREATE TABLE t1
(
col1 VARCHAR(10),
col2 VARCHAR(10)
)INSERT INTO t1
SELECT 'a001','aa'
UNION ALL
SELECT 'a003','bb'
UNION ALL
SELECT 'a05','cc'
UNION ALL
SELECT 'a0322','dd'
UNION ALL
SELECT 'a01','ee'SELECT col1,col2
FROM t1
WHERE CAST(SUBSTRING(col1,2,LEN(col1)) AS INT )
=(SELECT MAX(CAST(SUBSTRING(col1,2,LEN(col1)) AS INT )) FROM t1 )/*
结果:
col1 col2
---------- ----------
a0322 dd(1 row(s) affected)
*/
ORDER BY CAST(STUFF(col1, 1, 1, '') AS BIGINT) DESC
insert into #ta
select 'a11','aa' union all
select 'a23','nn' union all
select 'a45','ss' select * from #ta where coll in (select max(coll) from #ta)