php - Sql 请求到 2 个表以获取每行的百分比

Question

我有 2 个这样的 SQL 表:

___房间:

ROO_Id    ROO_Number
1         101
2         201

___预订:

BOO_Id    BOO_RoomNumber
1         1   
2         1
3         2

我想回显一张表格，其中包含我拥有的所有房间以及每个房间的百分比。

BOO_RoomNumber percentage count
101            66%         2 
201            33%         1

谢谢。

Answer 1

答案如下:

已更新

select r.ROO_Number BOO_RoomNumber, ((ifnull(cnt_book,0)*100)/(select count(*) from Bookings)) percentage, ifnull(cnt_book,0) `count`
from Rooms r left join (select BOO_RoomNumber, count(*) cnt_book
           from Bookings
           group by BOO_RoomNumber) cnt on r.ROO_Id=cnt.BOO_RoomNumber

工作中SQLFiddle