字符+数字的字段怎么判断大小
例如：abc-999,abc-1000怎么用max()判断abc-1000最大
[解决办法]
分三步走，1 截取，2 转换到数字，3 比较（max）
[解决办法]
WITH a1 (zd) AS
(
SELECT 'abc-999' UNION ALL
SELECT 'abc-1000'
)
,a2 AS
(
SELECT zd,CAST(RIGHT(zd,LEN(zd)-4) AS INT) zd2 FROM a1
)
SELECT TOP 1 zd FROM a2 ORDER BY zd2 desc
;WITH a1 (id,cstr) AS
(
SELECT 1,'abc-999,abc-1000'
)
SELECT a.id,b.cstr
into #temp
FROM (SELECT id, cstr=CONVERT(XML,'<root><item>'+replace(RTRIM(LTRIM(cstr)),',','</item><item>')+'</item></root>')
FROM a1) a
OUTER APPLY
(SELECT cstr=C.ColName.value('.','VARCHAR(100)')
FROM a.cstr.nodes('/root/item') C(ColName)) b
select cstr
from #temp a
where cast(replace(cstr,'abc-','') as int) in
(select MAX(cast(replace(cstr,'abc-','') as int)) from #temp )
/*
abc-1000
*/
