我有这两个表:
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
尝试这个。