php - php mysql 每天找出三个最畅销的产品

Question

我想找到每天销量最高的三种产品，并且将它们与销售数量一起显示。

但是，如果有多个产品共享相同的销售数量 我只想告诉有多少产品得到了这个排名。

我有两张 table

Products:
+-----+---------+
| Pid | Product |
+-----+---------+
|  1  | Moon    |
|  2  | Sun     |
|  3  | Venus   |
|  4  | Mars    |
+-----+---------+

SalesRows:
+-----+---------+------------+
| Pid | No_sold | Sales_date |
+-----+---------+------------+
|  1  |    1    | 2013-01-01 |
|  2  |    5    | 2013-01-01 |
|  3  |    2    | 2013-01-01 |
|  2  |    2    | 2013-01-01 |
+-----+---------+------------+

应该给:

+------+--------------+-------+
| Rank | Product      | Sales |
+------+--------------+-------+
|  1   | Sun          |   7   |
|  2   | Venus        |   2   |
|  3   | Moon         |   1   |
+------+--------------+-------+

然而这个销售数据:

SalesRows:
+-----+---------+------------+
| Pid | No_sold | Sales_date |
+-----+---------+------------+
|  1  |    1    | 2013-01-01 |
|  2  |    5    | 2013-01-01 |
|  3  |    2    | 2013-01-01 |
|  2  |    2    | 2013-01-01 |
|  4  |    1    | 2013-01-01 |
+-----+---------+------------+

应该给:

+------+--------------+-------+
| Rank | Product      | Sales |
+------+--------------+-------+
|  1   | Sun          |   7   |
|  2   | Venus        |   2   |
|  3   | *2 products* |   1   |
+------+--------------+-------+

对如何解决最后一部分有什么建议吗？

Answer 1

此查询可能对您有所帮助。

SELECT @rownum := @rownum + 1 rownum, 
       t.* 
  FROM (SELECT @rownum:=0) r, 
       (select case when indicator = 1 then Product
else concat( indicator, ' Products') end as Product, sales from (Select *, count(sales) as indicator from (SELECT Product,SUM(No_sold) AS sales FROM SalesRows
JOIN Products ON Products.Pid = SalesRows.Pid
WHERE Sales_date = curdate()
GROUP BY SalesRows.Pid ) a group by sales Order by sales desc) a) t