mysql - sql查询1个折扣表中的多个折扣

Question

我有一个如下所示的数据表:

---reservation_discount-----
id relation_id     title           rate
1  2               25% Discount    25
2  2               5% Discount     5

---reservation_hotel-----
id    room_id     price
2     1           2430

我的查询:

SELECT
reservation.customer,
hotel.title,
reservation_hotel.price,
reservation_discount.rate,
reservation_hotel.price * (100 - sum(reservation_discount.rate)) / 100 as total 
FROM
reservation_hotel
INNER JOIN reservation ON reservation_hotel.reservation_id = reservation.id
INNER JOIN hotel ON reservation_hotel.hotel_id = hotel.id
INNER JOIN room ON reservation_hotel.room_id = room.id AND room.hotel_id = hotel.id
INNER JOIN reservation_discount ON reservation_discount.relation_id = reservation_hotel.id
WHERE reservation_hotel.id=1 

我的问题是:我想让他按顺序接受折扣，但他没有得到 25%和 5% , 他得到 30% .

如何按顺序应用折扣？

Price: 2430 - 25% = 1822,5和 1822,5 - 5% = 1731.375

Answer 1

作为一个更复杂的算法，这个可以有多少折扣你想给多少，你也可以每次选择单独的折扣。

在这个例子中，我同时使用了 reservation_hotel.id ，但是您可以根据需要只使用 1 个甚至全部。在此选择查询中，reservation_hotel.id 称为 rh.id

这个选择语句

SELECT
  MIN(customer) customer,
  MIN(title) titke,
  MIN(price) price ,
  MIN(totalrate * 100) rate,
  MIN(total) total
FROM
  (SELECT
    rh.id id,
    r.customer customer,
    h.title title,
    rh.price price,
    rd.rate rate,
    if (@res = rh.id,@total :=@total,@total :=0) disc,
    if (@res = rh.id,@rate :=@rate,@rate :=0) disc2,
    if (@rate = 0, @rate := (100 - rd.rate)/ 100,@rate := @rate * (100 - rd.rate)/ 100) totalrate,
    if (@total=0,@total := rh.price * (100 - rd.rate) / 100,@total := @total * (100 - rd.rate) / 100) total,
    @res := rh.id        
  FROM
    reservation_hotel rh
    INNER JOIN reservation r ON rh.reservation_id = r.id
    INNER JOIN hotel h ON rh.hotel_id = h.id
    INNER JOIN room ro ON rh.room_id = ro.id AND ro.hotel_id = h.id
    INNER JOIN reservation_discount rd ON rd.relation_id = rh.id
    ,(SELECT @res := 0) r1,(SELECT @total := 0) r2,(SELECT @rate := 0) r3
  WHERE rh.id=1 or rh.id=2
  ORDER BY rh.id ) t1
GROUP BY id
ORDER BY id;

给你以下结果:

customer | titke         | price |  rate |    total:------- | :------------ | ----: | ----: | -------:John Doe | Premium Hotel |  2430 | 71.25 | 1731.375John Doe | Premium Hotel |  2250 | 71.25 | 1603.125

DBfiddle 示例 https://dbfiddle.uk/?rdbms=mysql_5.7&fiddle=276a5474b1329d018ffacdc910ea5f10

它计算出正确的客户实际费率和总金额。