具有多个LIMITS的MySQL查询 [英] MySQL Query with multiple LIMITS

问题描述

比方说，我有一张桌子，上面放着数百种各种颜色的玩具...

Let's say I have the following table with hundreds of toys of various colors...

---------------------------
  ITEM  |  COST  |  COLOR
---------------------------
    1   |  12.00 |  BLUE
    2   |  10.98 |  RED
    3   |   9.34 |  BLUE
    4   |  11.33 |  WHITE
    5   |   8.00 |  YELLOW
    .   |    .   |    .
    .   |    .   |    .
    .   |    .   |    .

如果我想选择价格最低的三个蓝色玩具，我当然可以写...

If I wanted to select the three lowest priced blue toys, I could of course write...

SELECT * FROM TOYS WHERE Color = 'BLUE' ORDER BY COST LIMIT 3;

但是，有没有办法让我选择唯一指定数量的价格最低的黄色，白色和蓝色玩具，就像我说的那样...

But, is there a way that I can select the a uniquely specified number of lowest priced yellow, white, and blue toys, as if I were saying...

 SELECT * FROM TOYS WHERE COLOR = 'YELLOW' (LIMIT 1) AND COLOR = 'WHITE' (LIMIT 3) AND COLOR = 'BLUE' (LIMIT 2) ORDER BY COST

是否有可能在MySQL中完全做到这一点而无需遍历PHP中的数组?

Is it possible to do this fully in MySQL without walking through the array in PHP?

推荐答案

为什么不呢?

select * from toys where color = 'Yellow' (LIMIT 1)
UNION
select * from toys where color = 'White' (LIMIT 3)
UNION
select * from toys where color = 'Blue' (LIMIT 2)

这篇关于具有多个LIMITS的MySQL查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！