Sql多条件排序

时间:2021-02-17 03:03:38

多条件排序可以通过在order by语句后面使用case when then条件语句来实现。

select * from 表名 ORDER BY case when 条件 then 0 else 1 end

 

例子:

1.创建表case_test

共有id,case_type,case_location,case_way四个字段。

2.导入数据:

INSERT INTO "XIANGZH"."case_test" VALUES ('1', '盗窃案', '台东', '技术开锁');
INSERT INTO "XIANGZH"."case_test" VALUES ('88', '谋杀案', '台东', '技术开锁');
INSERT INTO "XIANGZH"."case_test" VALUES ('99', '盗窃案', '江西路', '技术开锁');
INSERT INTO "XIANGZH"."case_test" VALUES ('5', '盗窃案', '台东', '暴力开锁');
INSERT INTO "XIANGZH"."case_test" VALUES ('6', '盗窃案', '江西路', '暴力开锁');
INSERT INTO "XIANGZH"."case_test" VALUES ('7', '谋杀案', '台东', '暴力开锁');
INSERT INTO "XIANGZH"."case_test" VALUES ('8', '谋杀案', '江西路', '技术开锁');
INSERT INTO "XIANGZH"."case_test" VALUES ('9', '谋杀案', '江西路', '暴力开锁');
INSERT INTO "XIANGZH"."case_test" VALUES ('10', '盗窃案', '台东', '技术开锁');

未排序截图:

Sql多条件排序

 

3.多条件分组排序

select * from "case_test" ORDER BY 
case when "case_type"='盗窃案' then 0 else 1 end,
case when "case_location" = '台东' then 0 else 1 end,
case when "case_way" = '技术开锁' then 0 else 1 end ASC

查询结果是按照条件分组排序(先按case_type排序,再按case_location排序,最后按case_way排序),结果截图:

Sql多条件排序

 

 4.满足条件个数排序 

select * from "case_test" ORDER BY 
case 
when "case_type"='盗窃案' and "case_location" = '台东' and "case_way" = '技术开锁' then 0
when "case_type"='盗窃案' and "case_location" = '台东' then 1
when "case_type"='盗窃案' and "case_way" = '技术开锁' then 2
when "case_location" = '台东' and "case_way" = '技术开锁' then 3
when "case_type"='盗窃案' then 4
when "case_location" = '台东' then 5
when "case_way" = '技术开锁' then 6
else 7
end

查询结果是按照满足条件的个数排序,截图:

Sql多条件排序

 

如果有更合理的sql写法,欢迎留言讨论。

 

参考:

ORACLE按条件排序的例子

Oracle怎么按条件排序