mysql - 如何将 SQL 查询结果存储在表列中

Question

我知道INSERT INTO table_name QUERY；但是，我不确定在这种情况下如何实现预期的结果。

这里有一个稍微做作的例子来解释我在找什么，但恐怕我不能把它说得更清楚。

我在为酒店设计的数据库中有两个表。

预订 和CUSTOMER_BOOKING

BOOKING 包含 PK_room_number、room_type 等，CUSTOMER_BOOKING 包含FK_room_number、FK_cusomer_id

CUSTOMER_BOOKING 是一个链接表(许多客户可以进行许多预订，许多预订可以包含许多客户)。

最终，在应用程序后端，我希望能够列出关联的客户少于 3 个的所有房间。我可以执行这个单独的查询并将结果保存在服务器端脚本中。

但是，更优雅的解决方案(从我的角度来看)是将其存储在 BOOKING 表本身中。即在CUSTOMER_BOOKING表中增加一列no_of_bookings，统计当前PK_room_number作为外键FK_room_number出现的次数。为什么要这样做呢？因为我不可能编写一个复杂的查询，它既包括来自所有 ROOMS 的信息以及其他表，也计算预订的发生次数，而不排除没有任何预订的 ROOMS。对于试图显示免费房间的酒店网站来说，这是一件非常糟糕的事情!

所以它看起来像这样

预订:PK_room_number (104B) room_type (double) room_price (high), no_of_bookings (3)预订:PK_room_number (108C) room_type (single) room_price (low), no_of_bookings (1)CUSTOMER_BOOKING:FK_room_number (104B) FK_customer_id (4312)CUSTOMER_BOOKING:FK_room_number (104B) FK_customer_id (6372)CUSTOMER_BOOKING:FK_room_number (104B) FK_customer_id (1112)CUSTOMER_BOOKING:FK_room_number (108C) FK_customer_id (9181)

我将如何着手创建它？

Answer 1

Because it would be impossible for me to write a single complicated query which will both include the information from all ROOMS, among other tables, and also count the occurrences of bookings, without excluding ROOMS that don't have any bookings.

我不会说这是不可能的，除非您遇到性能问题，否则它比添加新的摘要列更容易实现:

select b.*, count(cb.room_number) 
from bookings b
left join customer_booking cb on b.room_number = cb.room_number
group by b.room_number

根据您的查询，可能需要使用包含每个房间的预订计数的派生表来代替

select b.*, coalesce(t1.number_of_bookings,0) number_of_bookings
from bookings b
left join (
    select room_number, count(*) number_of_bookings
    from customer_booking
    group by room_number
) t1 on t1.room_number = b.room_number

如果房间在派生表中没有任何条目(即0 个预订)。

当您在每次计算预订数量时遇到性能问题时，汇总列是个好主意。在这种情况下，我建议在 customer_booking 表上创建 insert 和 delete 触发器，以增加或减少 number_of_bookings 列.