--> 测试数据:[TB]
if object_id('[TB]') is not null drop table [TB]
create table [TB]([ID] int,[S_Name] varchar(4),[S_Score] int,[S_Banji] int)
insert [TB]
select 1,'张三',89,4 union all
select 2,'李丽',91,3 union all
select 3,'赵六',88,4 union all
select 4,'王五',92,3 union all
select 5,'钱六',93,2 union all
select 6,'孙七',94,5select * from [TB]select * from TB
order by s_banji,s_score desc/*
ID S_Name S_Score S_Banji
5 钱六 93 2
4 王五 92 3
2 李丽 91 3
1 张三 89 4
3 赵六 88 4
6 孙七 94 5*/这样?
if object_id('[TB]') is not null drop table [TB]
create table [TB]([ID] int,[S_Name] varchar(4),[S_Score] int,[S_Banji] int)
insert [TB]
select 1,'张三',89,4 union all
select 2,'李丽',91,3 union all
select 3,'赵六',88,4 union all
select 4,'王五',92,3 union all
select 5,'钱六',93,2 union all
select 6,'孙七',94,5select * from [TB]select * from TB
order by s_banji,s_score desc/*
ID S_Name S_Score S_Banji
5 钱六 93 2
4 王五 92 3
2 李丽 91 3
1 张三 89 4
3 赵六 88 4
6 孙七 94 5*/这样?
CREATE TABLE s_tb(id INT,s_name VARCHAR(30),s_score DECIMAL(18,0),s_banji INT)INSERT INTO s_tb(id,s_name,s_score,s_banji)
select 1,'张三',89,4 union all
select 2,'李丽',91,3 union all
select 3,'赵六',88,4 union all
select 4,'王五',92,3 union all
select 5,'钱六',93,2 union all
select 6,'孙七',94,5SELECT s.id,s_name,s.s_score,s.s_banji FROM s_tb AS s WHERE s.s_banji < 4 UNION ALL
SELECT s.id,s_name,s.s_score,s.s_banji FROM s_tb AS s WHERE s.s_banji >= 4 ORDER BY s.s_score DESC
/*
id s_name s_score s_banji
----------- ------------------------------ -------------------- -----------
6 孙七 94 5
5 钱六 93 2
4 王五 92 3
2 李丽 91 3
1 张三 89 4
3 赵六 88 4
*/
as(
select num = case when s_banji<4 then 0 else 1 end ,* from TB)select id,S_name,S_score,s_banji
from TT
order by num,S_score desc
/*
id S_name S_score s_banji
5 钱六 93 2
4 王五 92 3
2 李丽 91 3
6 孙七 94 5
1 张三 89 4
3 赵六 88 4*/这样?