以下是样本数据,尽可能简化:
Table: items (id / manufacturer)
1 / Microsoft
2 / Microsoft
3 / Microsoft
4 / ACME
5 / Microsoft
Table: order_rows (item_id / date)
1 / 2012-12-01
1 / 2013-01-01
2 / 2013-01-02
2 / 2013-01-03
3 / 2013-01-04
3 / 2013-01-05
3 / 2013-01-06
最佳答案
您应该能够使用与此类似的方法连接表然后使用和 ORDER BY count(item_id) desc
按照您想要的顺序获取数据:
select i.id, i.manufacturer
from items i
left join order_rows o
on i.id = o.item_id
and o.date > '2013-01-01'
where i.manufacturer ='Microsoft'
group by i.id, i.manufacturer
order by count(o.item_id) desc;
manufacturer
从 SELECT 和 GROUP BY:select i.id
from items i
left join order_rows o
on i.id = o.item_id
and o.date > '2013-01-01'
where i.manufacturer ='Microsoft'
group by i.id
order by count(o.item_id) desc;
https://stackoverflow.com/questions/16450262/