590
社区成员
发帖
与我相关
我的任务
分享
with t1(bh) as
(
select '6C' union all
select '6D' union all
select '6B' union all
select '6C' union all
select '6D' union all
select '6E' union all
select '7' union all
select '8' union all
select '84' union all
select '9'
)
select *
from t1
order by case when bh like '%[a-z]%' then 9999999999 else CONVERT(int, bh) end, bh
if object_ID('tempdb..#A') is not null
drop table #A
Go
CREATE TABLE #A
(
ghksjjbh NVARCHAR(100)
)
Insert #A
select '6C' union all
select '6D' union all
select '6B' union all
select '6C' union all
select '6D' union all
select '6E' union all
select '7' union all
select '8' union all
select '84' union all
select '9'
--测试数据结束
SELECT *
FROM ( SELECT TOP 100
*
FROM #A
WHERE ISNUMERIC(ghksjjbh) = 1
ORDER BY CONVERT(INT, ghksjjbh)
) AS a
UNION ALL
SELECT *
FROM ( SELECT TOP 100
*
FROM #A
WHERE ISNUMERIC(ghksjjbh) = 0
ORDER BY ghksjjbh
) AS b
order by ISNUMERIC(col) desc, col
With T(Name) As
(
select '2' union all
select '33' union all
select '8' union all
select '33' union all
select '8' union all
select '45' union all
select '96' union all
select '84' union all
select '6B' union all
select '7C' union all
select '65D' union all
select '9a' union all
select '999'
)
Select *,case when isnumeric(name)=1 then 0 else 1 end as t from T group by Name
order by t asc,len(name) asc,name asc