34,594
社区成员
发帖
与我相关
我的任务
分享
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
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
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#那个也一样一个意思
[/quote]
你这个好点,上面那个如果数据行数大于5 还要修改。[/quote]
一样的,2,10,20是字符串,不这样做会排成 10,2,20
[/quote]
刚试了下,不会排成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;
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#那个也一样一个意思
[/quote]
你这个好点,上面那个如果数据行数大于5 还要修改。[/quote]
一样的,2,10,20是字符串,不这样做会排成 10,2,20
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#那个也一样一个意思
[/quote]
你这个好点,上面那个如果数据行数大于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#那个也一样一个意思
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