php - 根据另一个表列值计算字段总数

Question

room      | beds available | ****table room****
==================================
room1     | 4
room2     | 2 
room3     | 4

room      | occupant       | ****table occupant****
==================================
room1     | arnold
room1     | berry
room2     | charles
room2     | daisy
room3     | eric
room3     | frank
room3     | greg

我希望获得以下输出:

没有。有床位的房间数量:2 ||可提供床位的房间:1号房、3号房

我想我需要

1. 为每个独特的房间存储计数*并将计数存储为数组
2. 根据这个数组减去每个房间的可用床位
3. 有结果时显示房间名称 > 0

php 片段代码应该是什么样的？

Answer 1

这些 SQL 查询可以解决问题。

请记住，您可以删除不需要的列以节省您正在处理的数据量...

select r.id AS 'room',
    r.beds as 'total_beds',
    count(o.occupant) as 'taken_beds',
    r.beds-count(o.occupant) as 'free_beds'

FROM room r LEFT JOIN occupant o ON r.id = o.room
GROUP BY r.id

HAVING r.beds > count(o.occupant)

这只会返回未满的房间。

如果在任何其他时候，您也希望返回完整的房间，只需删除“HAVING”子句

select r.id AS 'room',
    r.beds as 'total_beds',
    count(o.occupant) as 'taken_beds',
    r.beds-count(o.occupant) as 'free_beds'

FROM room r LEFT JOIN occupant o ON r.id = o.room
GROUP BY r.id