DECLARE @TB TABLE([源数据] VARCHAR(9)) INSERT @TB SELECT '123456789' UNION ALL SELECT '00000003' UNION ALL SELECT '20120003' UNION ALL SELECT '123456789' UNION ALL SELECT '00000007' UNION ALL SELECT '20120007' UNION ALL SELECT '123456789' UNION ALL SELECT '00000001' UNION ALL SELECT '20120001' UNION ALL SELECT '123456789' UNION ALL SELECT '00000006' UNION ALL SELECT '20120006' UNION ALL SELECT '123456789' UNION ALL SELECT '00000002' UNION ALL SELECT '20120002'SELECT [源数据] FROM ( SELECT *,CASE WHEN [源数据]='123456789' THEN 0 WHEN LEFT([源数据],4)='0000' THEN 1 ELSE 2 END AS ORD, SEQ=ROW_NUMBER() OVER (PARTITION BY LEFT([源数据],4) ORDER BY [源数据]) FROM @TB) T ORDER BY SEQ,ORD /* 源数据 --------- 123456789 00000001 20120001 123456789 00000002 20120002 123456789 00000003 20120003 123456789 00000006 20120006 123456789 00000007 20120007 */
INSERT @TB
SELECT '123456789' UNION ALL
SELECT '00000003' UNION ALL
SELECT '20120003' UNION ALL
SELECT '123456789' UNION ALL
SELECT '00000007' UNION ALL
SELECT '20120007' UNION ALL
SELECT '123456789' UNION ALL
SELECT '00000001' UNION ALL
SELECT '20120001' UNION ALL
SELECT '123456789' UNION ALL
SELECT '00000006' UNION ALL
SELECT '20120006' UNION ALL
SELECT '123456789' UNION ALL
SELECT '00000002' UNION ALL
SELECT '20120002'SELECT [源数据]
FROM (
SELECT *,CASE WHEN [源数据]='123456789' THEN 0 WHEN LEFT([源数据],4)='0000' THEN 1 ELSE 2 END AS ORD,
SEQ=ROW_NUMBER() OVER (PARTITION BY LEFT([源数据],4) ORDER BY [源数据])
FROM @TB) T
ORDER BY SEQ,ORD
/*
源数据
---------
123456789
00000001
20120001
123456789
00000002
20120002
123456789
00000003
20120003
123456789
00000006
20120006
123456789
00000007
20120007
*/
简单方法:sql自带的导入\导出...