字段CAR
内容
赣A12345
赣B123456
京C12345
赣A66666
赣D11111
赣C12345
粤A12345
按下面的顺序排
赣A..........
赣B.....
赣C.........
赣D....
京C.........
粤A.......
或优先赣开头的在排其他的
内容
赣A12345
赣B123456
京C12345
赣A66666
赣D11111
赣C12345
粤A12345
按下面的顺序排
赣A..........
赣B.....
赣C.........
赣D....
京C.........
粤A.......
或优先赣开头的在排其他的
--> 生成测试数据: @T
DECLARE @T TABLE (内容 NVARCHAR(9))
INSERT INTO @T
SELECT '赣A12345' UNION ALL
SELECT '赣B123456' UNION ALL
SELECT '京C12345' UNION ALL
SELECT '赣A66666' UNION ALL
SELECT '赣D11111' UNION ALL
SELECT '赣C12345' UNION ALL
SELECT '粤A12345'--SQL查询如下:SELECT *
FROM @T
ORDER BY
CASE WHEN LEFT(内容,1)=N'赣' THEN 0 ELSE 1 END,
内容/*
内容
---------
赣A12345
赣A66666
赣B123456
赣C12345
赣D11111
京C12345
粤A12345(7 行受影响)
*/
INSERT INTO @T
SELECT '赣B123456' UNION ALL
SELECT '赣A12345' UNION ALL
SELECT '粤A12345' UNION ALL
SELECT '京C12345' UNION ALL
SELECT '赣A66666' UNION ALL
SELECT '赣D11111' UNION ALL
SELECT '赣C12345' SELECT * FROM @t ORDER BY CHARINDEX(LEFT(内容,1),'赣京粤'),内容
DECLARE @T TABLE (内容 NVARCHAR(9))
INSERT INTO @T
SELECT N'赣B123456' UNION ALL
SELECT N'赣A12345' UNION ALL
SELECT N'粤A12345' UNION ALL
SELECT N'京C12345' UNION ALL
SELECT N'赣A66666' UNION ALL
SELECT N'赣D11111' UNION ALL
SELECT N'赣C12345' SELECT * FROM @T
ORDER BY REPLACE(内容,N'赣',' ')
/*
内容
---------
赣A12345
赣A66666
赣B123456
赣C12345
赣D11111
京C12345
粤A12345(7 row(s) affected)
*/