使用单个查询获取多个表列的和
使用单个查询获取多个表列的和
提问于 2015-03-25 09:40:55
我有一个主桌和它的子表hotel_food、hotel_extras、hotel_room和参考Id 酒店，
现在，我想得到每个表(hotel_food、hotel_extras、hotel_room)中每个酒店的价格列之和。
现在我就这样用了
$result = $db->query("SELECT id FROM hotel WHERE id = '.$hotel.'");
if($row = $result->fetch_array(MYSQLI_ASSOC)) {
$food_result = $db->query("SELECT sum(price) FROM hotel_food WHERE hotel_id = '".$row['id']."'");
$room_result = $db->query("SELECT sum(price) FROM hotel_room WHERE hotel_id = '".$row['id']."'");
$extras_result = $db->query("SELECT sum(price) FROM hotel_extras WHERE hotel_id = '".$row['id']."'");
}有什么方法可以通过一个查询得到所有表的价格之和，如totalFoodPrice、totalRoomPrice、totalExtrasPrice
提前感谢
回答 2
Stack Overflow用户
回答已采纳
发布于 2015-03-25 09:50:19
您可以使用这样的子查询：
$result = $db->query("SELECT id,
(SELECT sum(price) FROM hotel_food WHERE hotel_id = h.id) TotalFoodPrice,
(SELECT sum(price) FROM hotel_room WHERE hotel_id = h.id) TotalRoomPrice,
(SELECT sum(price) FROM hotel_extras WHERE hotel_id = h.id) TotalExtraPrice
FROM hotel h WHERE id = '.$hotel.'");Stack Overflow用户
发布于 2015-03-25 09:44:44
SELECT * FROM
(
SELECT sum(price) AS totalFoodPrice FROM hotel_food WHERE hotel_id = 1,
SELECT sum(price) AS totalRoomPrice FROM hotel_room WHERE hotel_id = 1,
SELECT sum(price) AS totalExtrasPrice FROM hotel_extras WHERE hotel_id = 1
) temp页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/29252080
复制相关文章
相似问题
腾讯云开发者
