根据情况安排秩序

Question

我有一个查询：

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项目，不支持联合。感谢

Answer 1

不确定你的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 |
+------+-------+------+