tb_AAA
orderNo
a0031
a00389
a003555
a0035
a00312
a00398
a003155
得到:
orderNo
a003555
a003155
a00398
a00389
a00312
a0035
a0031请问怎么来实现哦??
orderNo
a0031
a00389
a003555
a0035
a00312
a00398
a003155
得到:
orderNo
a003555
a003155
a00398
a00389
a00312
a0035
a0031请问怎么来实现哦??
--> 生成测试数据: @T
DECLARE @T TABLE (orderNo VARCHAR(7))
INSERT INTO @T
SELECT 'a0031' UNION ALL
SELECT 'a00389' UNION ALL
SELECT 'a003555' UNION ALL
SELECT 'a0035' UNION ALL
SELECT 'a00312' UNION ALL
SELECT 'a00398' UNION ALL
SELECT 'a003155'--SQL查询如下:SELECT *
FROM @T
ORDER BY LEN(orderNo) DESC,orderNo DESC/*
orderNo
-------
a003555
a003155
a00398
a00389
a00312
a0035
a0031(7 行受影响)
*/
insert into tb values('a0031')
insert into tb values('a00389')
insert into tb values('a003555')
insert into tb values('a0035')
insert into tb values('a00312')
insert into tb values('a00398')
insert into tb values('a003155')
goselect * from tb order by cast(substring(orderNo , 4 , len(orderNo)) as int) descdrop table tb/*
orderNo
--------------------
a003555
a003155
a00398
a00389
a00312
a0035
a0031(所影响的行数为 7 行)
*/
declare @tb table(orderNo varchar(10))
insert @tb
SELECT 'a0031' UNION ALL
SELECT 'a00389' UNION ALL
SELECT 'a003555' UNION ALL
SELECT 'a0035' UNION ALL
SELECT 'a00312' UNION ALL
SELECT 'a00398' UNION ALL
SELECT 'a003155'select * from @tb order by cast(replace(orderNo,'a','') as int) desc
/*
orderNo
----------
a003555
a003155
a00398
a00389
a00312
a0035
a0031
*/
INSERT INTO @T
SELECT 'a0031' UNION ALL
SELECT 'a00389' UNION ALL
SELECT 'a003555' UNION ALL
SELECT 'a0035' UNION ALL
SELECT 'a00312' UNION ALL
SELECT 'a00398' UNION ALL
SELECT 'a003155'SELECT * FROM @T ORDER BY LEN(ORDERNO)DESC,ORDERNO DESC/**
a003555
a003155
a00398
a00389
a00312
a0035
a0031
select orderNo
from tb_AAA
order by cast(substring(orderNO,4,4) as int) desc
--语句一
select orderNo
from tb_AAA
order by cast(substring(orderNO,4,len(orderNo)-3) as int) desc--语句二
select *
from tb_AAA
order by len(orderNo) desc , orderNo desc