Mysql - 在价格表中选择低于，等于或高于的计数

Question

我有一个表，里面存储了各种产品的价格，想知道当我的价格低于、高于或等于其他记录时，如何查询返回。所有包含 idmonitor 零的记录仅表示这是我的价格，我将在同一天与其他记录进行比较。

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

我试图让这个查询已经过去几天了。

Answer 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

请注意，这是内存中的，我可能会遗漏一些东西。