如何从2表中减去乘数行？ MySQL的

I have been trying for hours search around for a way to subtract from multiplier row , from 2 table? As I keep getting error from mySQL workbench.

我一直试图寻找一种方法从2个表中减去乘数行?因为我不断从mySQL工作台得到错误。

I have 2 table hotel, hotel_stay

我有2桌酒店,hotel_stay

I need to get the number of unique room then minus the individual capacity

我需要获得独特房间的数量,然后减去个人容量

+--------------------+------------------+
hotel_stay
+--------------------+------------------+
room (PK)
99
99
10
10
10

+--------------------+------------------+
hotel  
+--------------------+------------------+ 
room(FK)  |   capacity
99        |   10
10        |   12

Output will be on the same table as the hotel or I will have to us AS?

输出将与酒店在同一张桌子上,或者我将与我们AS一起?

room | capacity
99   | 8      
10   | 9

1 个解决方案

#1

I am not sure I fully understand your question. But you can us something like this to get the total capacity for each room:

我不确定我完全理解你的问题。但是你可以用这样的东西来获得每个房间的总容量:

select h.room, 
   (h.capacity - hs.TotalBooked) as Capacity
from hotel h
inner join 
(
    select COUNT(room) as TotalBooked, 
       Room
    from hotel_stay
    group by room
) hs
    on h.room = hs.room

See SQL Fiddle with Demo. This returns the result:

请参阅SQL Fiddle with Demo。这将返回结果:

| ROOM | CAPACITY |
-------------------
|   10 |        9 |
|   99 |        8 |

#1