The order
Id hid rids startime (start time) endtime (end time)
1 December 11 2017-08-21 2017-08-25
.
Home
Hid hname
12 hotel 1
.
Room
Rids rname
11 rooms 1
.
According to the time period for every room in this period of time have order is 1, not 2

CodePudding user response:

The select room. Rids, room rname,
Case when the exists (select 1 from the order where the order. The rids=room. Rids and startime<=sysdate and endtime>=sysdate)
Then one else 2 end IsHaveOrder
The from room order by rids;

CodePudding user response:

Two tables outside connection, is empty of decode to 2

CodePudding user response:

Select r. *, case when o.i d is null then one else 2 end as the state from
Room as r
Left the join
(select id, rids the from the order when startime & gt;='2017-08-21' and endtimeOn r.r id=o.r id

CodePudding user response:

The SELECT a.h name, b.r name, decode (c.i d, null, 2, 1) as zhuangtai
FROM the home A, room B, order C
WHERE b.r id=c.r id (+)
AND A.h id=c. id (+)
C.s. tartime and (+) between: the start time and: end time
And c.e ndtime (+) between: the start time and: end time
The home table and room table should have to do no association will be a lot of duplicate data