SQL Server 2017

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE room
( [rid] int, [builder] Nvarchar(16));
INSERT INTO room
	([rid], [builder])
VALUES
(1,N'一宿A001'),
(2,N'一宿A002');
2 rows affected

select *
from room


rid
builder


1
一宿A001

2
一宿A002


CREATE TABLE bed
( [bid] int, [name] Nvarchar(8),[room_id] int,[enable] tinyint);
INSERT INTO bed
	([bid], [name],[room_id],[enable])
VALUES
(1,N'A床',1,1),	
(2,N'B床',1,0),	
(3,N'A床',2,1),	
(4,N'B床',2,1);
4 rows affected

select *
from bed


bid
name
room_id
enable


1
A床
1
1

2
B床
1
0

3
A床
2
1

4
B床
2
1


CREATE TABLE bed_history
( [hid] int, [bed_id] int,[c_time] datetime,[d_time] datetime,[user] nvarchar(4));
INSERT INTO bed_history
	([hid], [bed_id],[c_time],[d_time],[user])
VALUES
(1,1,'2018/10/4','2999/1/1',N'王大明'),
(2,3,'2018/11/1','2018/12/1',N'王小明');
2 rows affected

select [hid],[bed_id],CONVERT(varchar(12), c_time, 111) as 'c_time',
CONVERT(varchar(12), d_time, 111) as 'd_time',[user]
from bed_history


hid
bed_id
c_time
d_time
user


1
1
2018/10/04
2999/01/01
王大明

2
3
2018/11/01
2018/12/01
王小明


select room.*,bed.*
from room
left join bed on bed.room_id=room.rid
left join bed_history on bed_history.bed_id=bed.bid
where 1=1
and bed.enable=1
and bed_history.d_time is not null
and CONVERT(varchar(12), bed_history.d_time, 111)<CONVERT(varchar(12), GETDATE(), 111)


rid
builder
bid
name
room_id
enable


2
一宿A002
3
A床
2
1

hid	bed_id	c_time	d_time	user
1	1	2018/10/04	2999/01/01	王大明
2	3	2018/11/01	2018/12/01	王小明