如果你的URL都是这样的,直接简单点,这样就可以了取出数值后,按整型的方式排序SELECT CAST(SUBSTRING(URL,27,CHARINDEX('.htm',URL)-27)AS INT) ID
FROM TB
ORDER BY ID取出数值后,按字符串的方式排序SELECT SUBSTRING(URL,27,CHARINDEX('.htm',URL)-27) ID
FROM TB
ORDER BY ID
FROM TB
ORDER BY ID取出数值后,按字符串的方式排序SELECT SUBSTRING(URL,27,CHARINDEX('.htm',URL)-27) ID
FROM TB
ORDER BY ID
WITH a1 (url) AS
(
SELECT 'http://yao.xywy.com/goods/312051.htm' UNION ALL
SELECT 'http://yao.xywy.com/goods/402390.htm' UNION ALL
SELECT 'http://yao.xywy.com/goods/129721.htm' UNION ALL
SELECT 'http://yao.xywy.com/goods/221504.htm' UNION ALL
SELECT 'http://yao.xywy.com/goods/312050.htm' UNION ALL
SELECT 'http://yao.xywy.com/goods/402389.htm' UNION ALL
SELECT 'http://yao.xywy.com/goods/29563.htm' UNION ALL
SELECT 'http://yao.xywy.com/goods/129720.htm'
)
,a2 AS
(
SELECT url,REVERSE(url) url2
FROM a1
)
SELECT url,CAST(REVERSE(SUBSTRING(url2,CHARINDEX('.',url2)+1,CHARINDEX('/',url2)-CHARINDEX('.',url2)-1)) AS INT)
FROM a2
ORDER BY 2
create table x(c varchar(60))
go
insert into x(c)
values('http://yao.xywy.com/goods/312051.htm'),
('http://yao.xywy.com/goods/402390.htm'),
('http://yao.xywy.com/goods/129721.htm'),
('http://yao.xywy.com/goods/221504.htm'),
('http://yao.xywy.com/goods/312050.htm'),
('http://yao.xywy.com/goods/402389.htm'),
('http://yao.xywy.com/goods/29563.htm'),
('http://yao.xywy.com/goods/129720.htm')
go
select reverse(substring( reverse(c) , 5, charindex('/' , reverse(c)) -5 ))
from x
order by 1
go------------------------------------------------------------
129720
129721
221504
29563
312050
312051
402389
402390(8 行受影响)