mysql - 根据条件下订单
问题描述
我有一个查询:
SELECT * FROM Cars WHERE (cars.mark = "Audi" and cars.model = "a4" and cars.year = 2008) or
(cars.mark = "Audi" and cars.model <> "a4" and cars.year = 2008) or
(cars.mark <> "Audi" and cars.model <> "a4" and cars.year = 2008 )
我怎样才能按条件订购。第一个结果:cars.mark = "Audi" and cars.model = "a4" and cars.year = 2008
下一个结果:(cars.mark = "Audi" and cars.model <> "a4" and cars.year = 2008)
和第三个 : (cars.mark <> "Audi" and cars.model <> "a4" and cars.year = 2008 )
。
我不能使用联合查询,因为它是 Symfony 项目,它不支持联合。谢谢
解决方案
不确定您的 where 条件是否有用,基本上它说给我所有标记一个 2008 年的模型,我很确定该模型在所有标记中都是唯一的。至于结果的排序,您可以有条件地排序。
drop table if exists t;
create table t
(mark varchar(4) , model varchar(4), year int);
insert into t values
('audi','a3',2008),
('ford','st',2008),
('audi','a4',2008),
('gm','junk',2008),
('gm','junk',2009),
('ford','xx',2008);
SELECT * FROM t
WHERE year = 2008
order by
case when t.mark = "Audi" and t.model = "a4" then 1
when t.mark = "Audi" and t.model <> "a4" then 2
when t.mark <> "Audi" and t.model <> "a4" then 3
else 4
end ,
mark desc;
+------+-------+------+
| mark | model | year |
+------+-------+------+
| audi | a4 | 2008 |
| audi | a3 | 2008 |
| gm | junk | 2008 |
| ford | st | 2008 |
| ford | xx | 2008 |
+------+-------+------+
推荐阅读
- android - GMS 任务
类:如果 isSuccessful(),getResult() 可以返回 null 吗? - sql - MS Access 根据开始和结束日期自动填充查询列
- c - C分配char数组导致错误访问运行时异常
- fancybox-2 - V3 的 Fancybox V2 CSS
- cognos - Congnos 排名最低为 Rank 1
- groovy - Groovy - 添加类似于 JS 原型的方法?
- dart - 动态设置 Padding 小部件,使 FAB 不覆盖 Column
- python - Beautiful Soup(代码正在运行)现在只返回某些标签
- apache-spark - 将火花管道转换为数据框
- java - 在不同的线程上而不是在不同的进程上托管服务器和客户端之间有区别吗?