order
id hid rid startime(開始時間) endtime(結束時間)
1 12 11 2017-08-21 2017-08-25
...
home
hid hname
12 酒店1
...
room
rid rname
11 客房1
...
根據時間段查找每間客房在這段時間的有訂單就是1，沒有就是2
uj5u.com熱心網友回復：
select room.rid,room.rname,case when exists (select 1 from order where order.rid=room.rid and startime<=sysdate and endtime>=sysdate)
then 1 else 2 end IsHaveOrder
from room order by rid;
uj5u.com熱心網友回復：
兩表外連接下，為空的decode為2uj5u.com熱心網友回復：
select r.*,case when o.id is null then 1 else 2 end as state fromroom as r
left join
(select id,rid from order when startime >='2017-08-21' and endtime<='2017-08-25') as o
on r.rid = o.rid
uj5u.com熱心網友回復：
SELECT a.hname,b.rname,decode(c.id,null,2,1) as zhuangtaiFROM home A, room B, order C
WHERE b.rid = c.rid(+)
AND A.hid = C.hid(+)
and c.startime(+) between :開始時間 and :結束時間
and c.endtime(+) between :開始時間 and :結束時間
home 表 和 room 表 應該有關聯吧 沒關聯會出好多重復資料
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/83816.html
標籤：開發
下一篇：求個SQL陳述句