我的數據庫中有這張表：我需要一個顯示總成本、每月總成本、每月計數（roomId）、總計數（roomId）的查詢，按房間 ID 分組。我試過這個查詢，但結果并不像我需要的SELECT room_id, type, numberOfBeds, (SELECT SUM(total_cost) WHERE date_booked BETWEEN '".$dateFrom."' and '".$dateTo."') as monthlyIncome, SUM(total_cost) as totalIncome, COUNT(roomId) as totalReservations, (SELECT COUNT(roomId) WHERE date_booked BETWEEN '".$dateFrom."' and '".$dateTo."') as monthlyReservation FROM indvproj_rooms, indvproj_room_booking_details WHERE indvproj_rooms.room_id=indvproj_room_booking_details.roomId;
1 回答
白板的微信
TA貢獻1883條經驗 獲得超3個贊
問題不清楚。當你做 SUM 時，每月成本和總成本有什么區別？請檢查以下查詢和結果是否與您需要的相同。
SELECT
? ?*?
FROM
? ?(
? ? ? SELECT
? ? ? ? ?roomId,
? ? ? ? ?SUM(total_cost) as monthly_cost,
? ? ? ? ?COUNT(roomId) as monthly_count?
? ? ? FROM
? ? ? ? ?room?
? ? ? group by
? ? ? ? ?roomId,
? ? ? ? ?YEAR(date_booked),
? ? ? ? ?MONTH(date_booked)?
? ?)
? ?as r?
? ?LEFT JOIN
? ? ? (
? ? ? ? ?SELECT
? ? ? ? ? ? roomId as rid,
? ? ? ? ? ? SUM(total_cost) as total_cost,
? ? ? ? ? ? COUNT(roomId) as total_count?
? ? ? ? ?FROM
? ? ? ? ? ? room?
? ? ? ? ?group by
? ? ? ? ? ? roomId
? ? ? )
? ? ? as t?
? ? ? on r.roomId = t.rid
- 1 回答
- 0 關注
- 163 瀏覽
添加回答
舉報
0/150
提交
取消