我有一个表，存储各种产品的价格，想知道如何做一个查询返回，如果我的价格低于，高于或等于其他记录在同一天。所有包含idmonitor zero的记录都意味着这是我的价格，我将在同一天与其他记录进行比较。
id | idmonitor | idproduct | price | date
1 | 0 | 5 | 42.00 | 2012-06-01
2 | 1 | 5 | 45.00 | 2012-06-01
3 | 2 | 5 | 50.00 | 2012-06-01
4 | 0 | 6 | 22.00 | 2012-06-01
5 | 1 | 6 | 24.00 | 2012-06-01
6 | 2 | 6 | 26.00 | 2012-06-01
7 | 0 | 5 | 40.00 | 2012-06-03
8 | 1 | 5 | 40.00 | 2012-06-03
9 | 2 | 5 | 40.00 | 2012-06-03
10 | 0 | 6 | 30.00 | 2012-06-03
11 | 1 | 6 | 20.00 | 2012-06-03
12 | 2 | 6 | 10.00 | 2012-06-03
我想查询返回我一些像：
date | below | equal | above
2012-06-01 | 2 | 0 | 0
2012-06-02 | 2 | 0 | 0
2012-06-03 | 0 | 1 | 1
我试图使这个查询已经天了。
最佳答案
这样的查询虽然在性能方面不是最好的查询，但可能会起到以下作用：
select aux.date,
SUM(aux.below) as 'below',
SUM(aux.equal) as 'equal',
SUM(aux.above) as 'above'
from
(
select t1.date,
t1.idproduct,
(select count(1) from tablename t2 where t2.date = t1.date and t2.idproduct = t2.idproduct and t2.price > t1.price and t2.idmonitor <> 0) as 'below',
(select count(1) from tablename t3 where t3.date = t1.date and t3.idproduct = t3.idproduct and t3.price = t1.price and t3.idmonitor <> 0) as 'equal',
(select count(1) from tablename t4 where t4.date = t1.date and t4.idproduct = t4.idproduct and t4.price < t1.price and t4.idmonitor <> 0) as 'above',
from tablename t1
where t1.idmonitor = 0
) aux
group by aux.date
请注意，这是我的记忆，我可能遗漏了一些东西。
关于mysql - MySQL-在价格表中选择低于或等于等于的计数，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/11000846/