I have a table 'goods' with 3 columns (id, name, price). I need to get data by list of ids and price range.
I'm using this query:
SELECT id, name, price FROM goods
WHERE (id, price)
IN ((1,10), (3,20), (2,10))
ORDER BY FIELD(id, 1,3,2)
Everything is just fine while I'm using strict price values.
In fact, I need to do query like:
SELECT id, name, price FROM goods
WHERE (id, price)
IN ((1, BETWEEN 0 AND 100), (2, BETWEEN 50 AND 150), (n, BETWEEN m AND k))
ORDER BY FIELD(id, 1,3,n)
I found expression (1, BETWEEN 0 AND 100)
will never return the result I need, while 开发者_如何学JAVA(1,10)
will.
I'm surely can exclude records by price range in php, but I'm trying to find more efficient way to do it in mysql query using indexes. What should I do?
You can't use BETWEEN like that. Try changing your IN expression to a series of ORs instead:
SELECT id, name, price
FROM goods
WHERE
(id = 1 AND price BETWEEN 0 AND 100) OR
(id = 2 AND price BETWEEN 50 AND 150) OR
(id = n AND price BETWEEN m AND k)
ORDER BY FIELD(id, 1,3, n)
精彩评论