Select * From ( Select 'aa' as cn union Select '2' union Select 'bb' union Select '10' union Select '20' ) a Order By case cn when 'aa' then 0 when 'bb' then 4 when '2' then 1 when '10' then 2 else 3 end
SELECT cn,CASE WHEN cn='a' THEN -999 WHEN cn='b' THEN 999 ELSE cn END AS cn_new FROM ( SELECT 'a' AS cn UNION SELECT 'b' UNION SELECT '111' UNION SELECT '222' UNION SELECT '333' ) a ORDER BY cn_new 3#那个也一样一个意思
SELECT cn,CASE WHEN cn='a' THEN -999 WHEN cn='b' THEN 999 ELSE cn END AS cn_new FROM ( SELECT 'a' AS cn UNION SELECT 'b' UNION SELECT '111' UNION SELECT '222' UNION SELECT '333' ) a ORDER BY cn_new 3#那个也一样一个意思 你这个好点,上面那个如果数据行数大于5 还要修改。
SELECT cn,CASE WHEN cn='a' THEN -999 WHEN cn='b' THEN 999 ELSE cn END AS cn_new FROM ( SELECT 'a' AS cn UNION SELECT 'b' UNION SELECT '111' UNION SELECT '222' UNION SELECT '333' ) a ORDER BY cn_new 3#那个也一样一个意思 你这个好点,上面那个如果数据行数大于5 还要修改。一样的,2,10,20是字符串,不这样做会排成 10,2,20
--测试数据 if not object_id(N'Tempdb..#T') is null drop table #T Go Create table #T([cn] nvarchar(22)) Insert #T select N'aa' union all select N'2' union all select N'bb' union all select N'10' union all select N'20' Go --测试数据结束 SELECT * FROM #T ORDER BY CASE WHEN cn = 'aa' THEN ( SELECT MIN(CONVERT(INT, cn)) FROM #T WHERE cn NOT IN ( 'aa', 'bb' ) ) - 1 WHEN cn = 'bb' THEN ( SELECT MAX(CONVERT(INT, cn)) FROM #T WHERE cn NOT IN ( 'aa', 'bb' ) ) + 1 ELSE CONVERT(INT, cn) END;
用case when判断就行了
SELECT cn,CASE WHEN cn='a' THEN -999 WHEN cn='b' THEN 999 ELSE cn END AS cn_new FROM ( SELECT 'a' AS cn UNION SELECT 'b' UNION SELECT '111' UNION SELECT '222' UNION SELECT '333' ) a ORDER BY cn_new 3#那个也一样一个意思 你这个好点,上面那个如果数据行数大于5 还要修改。一样的,2,10,20是字符串,不这样做会排成 10,2,20
刚试了下,不会排成10.2.20呢
Select * From ( Select 'aa' as cn union Select '2' union Select 'bb' union Select '10' union Select '20' ) a Order By case isnumeric(cn) when 0 then case cn when 'aa' then -10000 when 'bb' then 100000 else 1 end when 1 then cast(cn as int) end
字母排在数字后, 在 'bb‘之前Select * From ( Select 'aa' as cn union Select '2' union Select 'bb' union Select '10' union Select '20' ) a Order By case isnumeric(cn) when 0 then case cn when 'aa' then -100000 when 'bb' then 100000 else 99999 end when 1 then cast(cn as int) end
From (
Select 'aa' as cn union
Select '2' union
Select 'bb' union
Select '10' union
Select '20'
) a
Order By case cn when 'aa' then 0
when 'bb' then 4
when '2' then 1
when '10' then 2
else 3 end
WHEN cn='b' THEN 999
ELSE cn END AS cn_new
FROM (
SELECT 'a' AS cn
UNION
SELECT 'b'
UNION
SELECT '111'
UNION
SELECT '222'
UNION
SELECT '333'
) a
ORDER BY cn_new
3#那个也一样一个意思
WHEN cn='b' THEN 999
ELSE cn END AS cn_new
FROM (
SELECT 'a' AS cn
UNION
SELECT 'b'
UNION
SELECT '111'
UNION
SELECT '222'
UNION
SELECT '333'
) a
ORDER BY cn_new
3#那个也一样一个意思
你这个好点,上面那个如果数据行数大于5 还要修改。
WHEN cn='b' THEN 999
ELSE cn END AS cn_new
FROM (
SELECT 'a' AS cn
UNION
SELECT 'b'
UNION
SELECT '111'
UNION
SELECT '222'
UNION
SELECT '333'
) a
ORDER BY cn_new
3#那个也一样一个意思
你这个好点,上面那个如果数据行数大于5 还要修改。一样的,2,10,20是字符串,不这样做会排成 10,2,20
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([cn] nvarchar(22))
Insert #T
select N'aa' union all
select N'2' union all
select N'bb' union all
select N'10' union all
select N'20'
Go
--测试数据结束
SELECT *
FROM #T
ORDER BY CASE WHEN cn = 'aa' THEN ( SELECT MIN(CONVERT(INT, cn))
FROM #T
WHERE cn NOT IN ( 'aa', 'bb' )
) - 1
WHEN cn = 'bb' THEN ( SELECT MAX(CONVERT(INT, cn))
FROM #T
WHERE cn NOT IN ( 'aa', 'bb' )
) + 1
ELSE CONVERT(INT, cn)
END;
WHEN cn='b' THEN 999
ELSE cn END AS cn_new
FROM (
SELECT 'a' AS cn
UNION
SELECT 'b'
UNION
SELECT '111'
UNION
SELECT '222'
UNION
SELECT '333'
) a
ORDER BY cn_new
3#那个也一样一个意思
你这个好点,上面那个如果数据行数大于5 还要修改。一样的,2,10,20是字符串,不这样做会排成 10,2,20
刚试了下,不会排成10.2.20呢
Select *
From (
Select 'aa' as cn union
Select '2' union
Select 'bb' union
Select '10' union
Select '20'
) a
Order By case isnumeric(cn)
when 0
then case cn
when 'aa' then -10000
when 'bb' then 100000
else 1 end
when 1
then cast(cn as int)
end
From (
Select 'aa' as cn union
Select '2' union
Select 'bb' union
Select '10' union
Select '20'
) a
Order By case isnumeric(cn)
when 0
then case cn
when 'aa' then -100000
when 'bb' then 100000
else 99999 end
when 1
then cast(cn as int)
end