Multiple WHERE with LIMIT MySQL
Using PHP, is it possible to select multiple rows from one table, in a MySQL database, with different WHERE clauses having a specific LIMIT for each WHERE clause.
For example:
SELECT * FROM the_table WHERE color = 'blue' LIMIT 5 OR colo开发者_Python百科r = 'red' LIMIT 10
I know the above statement does not work. But is there a way to do this with a single call to the database?
You can use a UNION, if I understand your post correctly:
(SELECT * FROM the_table WHERE color = 'blue' LIMIT 5)
UNION ALL
(SELECT * FROM the_table WHERE color = 'red' LIMIT 10)
SELECT * FROM the_table WHERE color = 'blue' LIMIT 5
UNION
SELECT * FROM the_table WHERE color = 'red' LIMIT 10 ;
Have a look at the official MySQL documentation regarding subqueries (there's a reference made to LIMITs here, and some cases they may not be valid in): http://dev.mysql.com/doc/refman/5.5/en/subquery-restrictions.html -- you may find an optimisation using subqueries in there, but UNION is probably quite fast and will probably be auto-optimised.
I believe flow control in MySQL is what you're looking for.
精彩评论