我有这两个表:

product
id | name
1  |  A
2  |  B

history
id_product | year | month | percent
1          | 2014 |   2   |  0
1          | 2014 |   3   |  20
1          | 2014 |   4   |  60


如果月份是当前月份或前一个月,如何获得最高百分比,例如:

SELECT * FROM history
WHERE id_product IN
     (SELECT id FROM product
      WHERE id = 1)
  AND ((year = 2014 AND month = 3) OR
       (year = 2014 AND month = 2))
ORDER BY year DESC, month DESC


它将输出:20,但实际上显示为0,它总是给我一个月前的价值...欢迎任何建议...谢谢!

最佳答案

SELECT max(hs.`percent`) as `percent`, hs.id_product, p.name FROM
(SELECT *
FROM history
WHERE ((year = 2014 AND month = 3) OR
       (year = 2014 AND month = 2))
) as hs
LEFT JOIN `product` as p on p.id=hs.id_product
GROUP BY hs.id_product


尝试这个。

10-05 22:46
查看更多