MySQL按另一张表的SUM字段查询顺序

Question

我有 3 个表:

hotel : id, hotel_name

hotel_supplier:id, supplier_name

hotel_price:id, hotel_id, supplier_id, allotment, price

我想按酒店价格的总和(配额)降序从酒店订单中选择所有，所以结果从配额最多的酒店排序。如何做到这一点..？，任何想法感激收到...，谢谢。

sorry, i am forget one thing, there is another condition, on hotel_price if price == 0 then allotment of the row is not count in sum(allotment).

Answer 1

试试这个变体:

select h.id, h.hotel_name, sum(hp.allotment)
  from hotel h join hotel_price hp on (h.id = hp.hotel_id)
 group by h.id, h.hotel_name
 order by 3 desc;