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

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)

这只会返回未满的房间。

​

​

如果在任何其他时间点，您也希望返回满房间，只需删除“有”子句。

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

​

​

Answer 2

我认为这一切都可以通过一个简单的SQL查询来完成，这个查询应该如下所示：

SELECT room.beds AS beds, COUNT(occupant.occupant) AS beds_occupied
FROM room
LEFT JOIN occupant ON occupant.room = room.room
GROUP BY room
HAVING (beds - beds_occupied) > 0;

您可以使用'HAVING‘子句，它与WHERE子句相似，但适用于聚合。我还没有在您的确切表上运行这个确切的查询，因此可能会有一个错误，但是，我希望这个查询应该做的事情是清楚的。

Answer 3

MySQL提供GROUP_CONCAT来聚合字符串：

select 
  count(*) as number_of_rooms, 
  group_concat(room) as rooms
from room r
where beds_available >
(
  select count(*)
  from occupant o
  where o.room = r.room
);

这将选择可用床位比占用的房间多的房间，然后将产生的行合并成一行，其中包含可用房间的数量和一个房间名称逗号分隔的字符串。