MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select version();


version()


8.0.23


create table room (roomNo int, seatNum int);
insert into room values
(1,	30),
(2,	20),
(3,	10),
(4,	25),
(5,	10);

create table people (ID int,	RoomNo int,	TimeLeave time);
insert into people values
(101,	1,"15:47:20"),
(102,	5,NULL),
(103,	4,"15:40:20"),
(104,	2,NULL),
(105,	2,NULL);
SELECT * FROM room;
SELECT * FROM people;


roomNo
seatNum


1
30

2
20

3
10

4
25

5
10




ID
RoomNo
TimeLeave


101
1
15:47:20

102
5
null

103
4
15:40:20

104
2
null

105
2
null


SELECT room.roomno,
       max(room.seatNum)-sum(case when people.roomno is null then 0 when people.roomno is not null and people.TimeLeave is not null then 0 else 1 end) as AvailableSeatNum,
       sum(case when people.roomno is null then 0 when people.roomno is not null and people.TimeLeave is not null then 0 else 1 end) as NotAvailable
FROM room 
LEFT JOIN people 
ON room.roomNo=people.roomNo
group by room.roomno


roomno
AvailableSeatNum
NotAvailable


1
30
0

2
18
2

3
10
0

4
25
0

5
9
1


SELECT rp.roomno, 
       MAX(rp.seatnum)-SUM(rp.cval) AS AvailableSeatNum, 
       SUM(rp.cval) AS NotAvailable
FROM
(SELECT r.roomno, r.seatNum, p.id, p.TimeLeave,
       CASE WHEN p.roomno IS NULL THEN 0 
            WHEN p.roomno IS NOT NULL AND p.TimeLeave IS NOT NULL THEN 0 
       ELSE 1 END AS cval 
FROM room AS r
LEFT JOIN people AS p
ON r.roomNo=p.roomNo) rp
GROUP BY rp.roomno;


roomno
AvailableSeatNum
NotAvailable


1
30
0

2
18
2

3
10
0

4
25
0

5
9
1