34,593
社区成员
发帖
与我相关
我的任务
分享
with TT
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*/
IF OBJECT_ID('s_tb') IS NOT NULL DROP TABLE s_tb
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,5
SELECT 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
*/
--> 测试数据:[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,5
select * 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*/