请教SQL 排序问题

[Mr_yang_da]

比如两列id 和 numb,
id numb
1 10
2 11
3 12
4 13
5 14
6 15
7 16
8 17
9 18
10 19



怎样让他按照大于等于15的排到前边按照正序，小于15的排到后边（按ID倒序）
结果如下：15,16,17,18,19,14,13,12,11,10

[霜寒月冷]

if object_id('[tb1]') is not null drop table [tb1]
go
create table tb1 (id int,numb int )
insert into tb1

select 1,10    union all                             
select 2,11    union all                             
select 3,12    union all                             
select 4,13    union all                             
select 5,14    union all                             
select 6,15    union all                             
select 7,16    union all                             
select 8,17    union all                             
select 9,18    union all                             
select 10,19                                         

go

select id ,numb  from 
(
select id ,numb,1 as s from tb1 where numb>=15
union all
select id ,numb,0 as s from tb1 where numb<15 
)
t  order by s desc, (case when  s=1 then numb end) asc ,(case when  s=0 then numb end) desc

--id	numb
--6	15
--7	16
--8	17
--9	18
--10	19
--5	14
--4	13
--3	12
--2	11
--1	10

[黑暗中的光芒]

USE tempdb
IF OBJECT_ID('[TB1]') IS NOT NULL DROP TABLE [TB1]
GO
CREATE TABLE TB1 (ID INT,NUMB INT )
INSERT INTO TB1
 
SELECT 1,10    UNION ALL                             
SELECT 2,11    UNION ALL                             
SELECT 3,12    UNION ALL                             
SELECT 4,13    UNION ALL                             
SELECT 5,14    UNION ALL                             
SELECT 6,15    UNION ALL                             
SELECT 7,16    UNION ALL                             
SELECT 8,17    UNION ALL                             
SELECT 9,18    UNION ALL                             
SELECT 10,19                                         
 
GO
 
SELECT * FROM TB1  
ORDER BY CASE WHEN NUMB>='15' THEN 1
                   ELSE 2 END ASC

/*
ID	NUMB
6	15
7	16
8	17
9	18
10	19
1	10
2	11
3	12
4	13
5	14
*/ 

[快溜]

select * from tb order by case SIGN(15-numb) when 0 then -1 else SIGN(15-numb) end,SIGN(15-numb)*numb desc

[黑暗中的光芒]

再加一层排序

USE tempdb
IF OBJECT_ID('[TB1]') IS NOT NULL DROP TABLE [TB1]
GO
CREATE TABLE TB1 (ID INT,NUMB INT )
INSERT INTO TB1
 
SELECT 1,10    UNION ALL                             
SELECT 2,11    UNION ALL                             
SELECT 3,12    UNION ALL                             
SELECT 4,13    UNION ALL                             
SELECT 5,14    UNION ALL                             
SELECT 6,15    UNION ALL                             
SELECT 7,16    UNION ALL                             
SELECT 8,17    UNION ALL                             
SELECT 9,18    UNION ALL                             
SELECT 10,19                                         
 
GO
 
SELECT * FROM TB1  
ORDER BY CASE WHEN NUMB>='15' THEN 1
                   ELSE 2 END ,NUMB DESC 
 

/*
-------
ID	NUMB
10	19
9	18
8	17
7	16
6	15
5	14
4	13
3	12
2	11
1	10

*/