我知道这是一个愚蠢的问题，但我是 php 的新手，所以任何帮助都很有用。
我有两个表，product和payment：
- 产品表有
Id，Room_Available，price， ... - 付款表有
Id，Product_Id，Total_Room， ... - 产品表有多条记录
我期望的是，如果Occupied_Room <= Room_Available，条件失败，则提供所有产品记录，因为状态="Y"，否则状态="N"。
-
cms_product.Room_Available是总可用客房 -
cms_payment.Total_Room是客房预订
查询为：
SELECT SUM(cms_payment.Total_Room) as Occupied_Room,
`cms_product`.`Room_Available` as Available_Room
FROM
(
`cms_product`
LEFT JOIN `cms_payment` on (cms_product.`Id` = cms_payment.`Product_Id`)
)
GROUP BY `cms_product`.`Id`
HAVING Occupied_Room <= Room_Available
试试这个： 下表使用了一些示例数据。您可以将状态更改为y或n :)
SQLFIDDLE 演示
select product.id,
case when coalesce(x.sm,0) <= product.total_room then
'available' else 'unavailable' end status,
product.total_room, coalesce(x.sm,0) as rooms_required
from product
left join (
select payment.product_id,
sum(payment.room_required) sm
from payment
group by payment.product_id)x
on x.product_id = product.id
group by product.id
;
| ID | STATUS | TOTAL_ROOM | ROOMS_REQUIRED |
--------------------------------------------------
| 1 | unavailable | 10 | 12 |
| 2 | available | 15 | 15 |
| 3 | available | 5 | 4 |
| 4 | available | 10 | 0 |
这在黑暗中有点刺痛，但我认为你想要：
SELECT Prod.Id,
CASE WHEN COALESCE(SUM(Pay.Total_Room),0) <= Prod.Room_Available THEN 'Y'
ELSE 'N'
END AS Status
FROM cms_product AS Prod
LEFT JOIN cms_payment AS Pay ON Prod.Id = Pay.Product_Id
GROUP BY Prod.Id, Prod.Room_Available
这将显示所有产品 ID 和状态 （ 'Y' = 可用或已满; 'N' =超额预订）。