>我有一个数据表,看起来像这样:
---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
作为一种更复杂的算法,它可以提供任意数量的折扣,您也可以每次选择单独的折扣。
在这个例子中,我同时使用了 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;
给出以下结果:
客户 | Titke | 价格 | 费率 | 总 :------- |:------------ |----: |----: |-------: 约翰·多伊 |高级酒店 | 2430 |71.25 |1731.375 约翰·多伊 |高级酒店 | 2250 |71.25 |1603.125
DBfiddle 示例 https://dbfiddle.uk/?rdbms=mysql_5.7&fiddle=276a5474b1329d018ffacdc910ea5f10
它计算客户的正确实际费率和总金额。
要以通用方式解决此问题,您需要某种递归。如果您运行的是MySQL 8.0,则可以使用递归公用表表达式。
这个想法是最初将reservation_hotel
与id
最小的reservation_discount
记录联接(使用相关子查询(,并应用第一个折扣。然后,我们得到下一个折扣,并将其应用于先前计算的价格。
此查询为您提供分步计算:
with recursive cte as (
select r.*, r.price * (100 - d.rate) / 100 final_price, d.id discount_id
from reservation_hotel r
inner join reservation_discount d on d.id = (
select min(d1.id) from reservation_discount d1 where d1.relation_id = r.id
)
union all
select c.id, c.room_id, c.price, c.final_price * (100 - d.rate) / 100, d.id
from cte c
inner join reservation_discount d on d.id = (
select min(d1.id)
from reservation_discount d1
where d1.relation_id = c.id and d1.id > c.discount_id
)
)
select * from cte order by discount_id
ID | room_id | 价格 | final_price | discount_id -: |------: |----: |----------: |----------: 2 | 1 | 2430 | 1822.5000 | 1 2 | 1 | 2430 | 1731.3750 | 2
如果您只想要最终价格,那么您可以使用聚合:
with recursive cte as (
select r.*, r.price * (100 - d.rate) / 100 final_price, d.id discount_id
from reservation_hotel r
inner join reservation_discount d on d.id = (
select min(d1.id)
from reservation_discount d1
where d1.relation_id = r.id
)
union all
select c.id, c.room_id, c.price, c.final_price * (100 - d.rate) / 100, d.id
from cte c
inner join reservation_discount d on d.id = (
select min(d1.id)
from reservation_discount d1
where d1.relation_id = c.id and d1.id > c.discount_id
)
)
select id, room_id, price, min(final_price) final_price
from cte
group by id, room_id, price
ID | room_id | 价格 | final_price -: |------: |----: |----------: 2 | 1 | 2430 | 1731.3750
DB小提琴演示