开发者

Price filter using mysql

I would like to create a price filter from following table. Currently works with two mysql query first one create price range and second will create count by an ajax call.

Price filter using mysql

Is there any other way to get both in single query.

Expected result:

300- 400 (2)
400- 500 (2)
500- 600 (2)
6开发者_Go百科00- 620 (2)


Lets say this query returns your price range:

SELECT a, b FROM x WHERE ... blah blah blah

Now. If you want to count this is what i would do:

SELECT 
     CONCAT(y.a,'-',y.b) "range",
     (SELECT COUNT(*) FROM item WHERE price BETWEEN y.a AND y.b) "total"
FROM 
     (SELECT a, b FROM x) AS y

I think it should work. I don't know if is the best way but give it a try?


Assuming you have a table price_range with low and high columns:

SELECT 
    CONCAT(low, '-', high) as range,
    sum(price between low and high) as total
FROM price_range
CROSS JOIN item

If you don't have a table, but have a query instead, do similar to above, but with the query aliased:

SELECT 
    CONCAT(low, '-', high) as range,
    sum(price between low and high) as total
FROM (select low, high from some_table) as price_range
CROSS JOIN item
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜