我有下表。
id | car_name | owner
-------------------------
1 | Toyota | Jan
2 | Ford | Mike
3 | Isuzu | Andrew
4 | BMW | Jan
5 | Ferrari | Steve
6 | Audi | Jan
7 | Benz | Klark
8 | Hyundai | Jan
9 | Kia | Jan
我想要获取所有车主,但 Jan 有 5 辆车,我希望 Jan 的前四项出现在列表中。我不关心我收到其余物品的顺序。像这样。
id | car_name | owner
-------------------------
1 | Toyota | Jan
4 | BMW | Jan
7 | Benz | Jan
8 | Hyundai | Jan
2 | Ford | Mike
3 | Isuzu | Andrew
5 | Ferrari | Steve
6 | Audi | Bob
9 | Kia | Jan
我怎样才能做到这一点?谢谢
您可以通过以下任一方式订购
order by owner <> 'Jan'
order by owner = 'Jan' desc
order by case when owner = 'Jan' then 0 else 1 end
order by if(owner = 'Jan',0,1)
owner = 'Jan'
产生1
for True
and 0
for False
,因此为什么desc
另外,由于您不关心除前四行之外的其余行的顺序,因此您不妨继续保留 Jan 行的其余部分。
尝试这个:
SELECT
id, car_name, owner
FROM
((SELECT
0 x, t.*
FROM
your_table t
ORDER BY owner <> 'Jan' , id
LIMIT 4) UNION ALL (SELECT
*
FROM
(SELECT
1 x, t.*
FROM
your_table t
ORDER BY owner <> 'Jan' , id
LIMIT 4 , 1000) t
ORDER BY id)) t
ORDER BY x , id;
仅当 Jan 有 4 行或更多行时才有效。
SQLFiddle http://sqlfiddle.com/#!9/68b2ca/7
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)