请教大哥大嫂一个SQL语句我这时有两张表,结构如下:房型表(roomType):房型编号(rt_Id),房间总数(rt_num)
线上订房表(bookA):订房编号(a_Id),订房数量(a_num),入住时间(a_sTime),退房时间(a_eTime),房型编号(a_rtId)
线下订房表(bookB):订房编号(b_Id),订房数量(b_num),入住时间(b_sTime),退房时间(b_eTime),房型编号(b_rtId)
现想通过SQL语句得到如下功能:
用户输入房间数(如:bookNum),与入住时间(bookSTime),退房时间(bookETime)查询到可用的房型
注:只要在用户输入的入住时间与退房时间期间有一天的可用房间不足用户所输入的房间数都视为不可用房型
线上订房表(bookA):订房编号(a_Id),订房数量(a_num),入住时间(a_sTime),退房时间(a_eTime),房型编号(a_rtId)
线下订房表(bookB):订房编号(b_Id),订房数量(b_num),入住时间(b_sTime),退房时间(b_eTime),房型编号(b_rtId)
现想通过SQL语句得到如下功能:
用户输入房间数(如:bookNum),与入住时间(bookSTime),退房时间(bookETime)查询到可用的房型
注:只要在用户输入的入住时间与退房时间期间有一天的可用房间不足用户所输入的房间数都视为不可用房型
select b.a_rtId,count(1) as tmpCount
from(select * from bookA
union
select * from bookB
) b
where bookSTime between b.a_sTime and b.a_eTime
or bookETime between b.a_sTime and b.a_eTime
group by b.a_rtId
having tmpCount > bookNum - 1类似这个写法吧,具体小错误自己修改下
实际表中 bookA 与 bookB的结构是不同的,所以这里union不成功
不好意思,小弟第一次发帖,有点乱了~~
并不是一条预订表记录表示一个房间,所以count(1)不太对,而是用户可以一次订好几个房间的,“订房数量”没用上
2》select * from bookA
union
select * from bookB
你自己完全可以改下嘛!!select A.a_Id as id,A.a_rtId as rtId, .... from bookA A
union
select B.a_Id as id,B.a_rtId as rtId, .... from bookB B这样得到的结构就可以相同了!!!
select b.rtId,sum(num) as tmpCount
from(select A.a_rtId as rtId,
A.a_num as num,
A.a_sTime as sTime
A.a_eTime as eTime
from bookA A
union
select B.a_rtId as rtId,
B.a_num as num,
B.a_sTime as sTime
B.a_eTime as eTime
from bookB B
) b,roomType r
where bookSTime between b.sTime and b.eTime
or bookETime between b.sTime and b.eTime
and b.rtId = r.rt_Id
group by b.rtId
having r.rt_num - tmpCount > bookNum - 1
select b.rtId,
sum(b.num) as tmpCount
from(select A.a_rtId as rtId,
A.a_num as num,
A.a_sTime as sTime,
A.a_eTime as eTime
from bookA A
union
select B.b_rtId as rtId,
B.b_num as num,
B.b_sTime as sTime,
B.b_eTime as eTime
from bookB B
) b,roomType r
where bookSTime between b.sTime and b.eTime
or bookETime between b.sTime and b.eTime
and b.rtId = r.rt_Id
group by b.rtId
having r.rt_num - tmpCount > bookNum - 1
select R.rt_Id,R.rt_num-isnull(A.a_num,0)-isnull(B.b_num,0), from roomType R left join bookA a on R.rt_Id=A.a_rtId join bookB b on R.rt_Id=B.b.rtId
where datediff(dd,cast(A.a_eTime as datetime),@bookSTime)>1 and datediff(dd,cast(B.b_eTime as datetime),@bookSTime)>1 and cast(A.a_eTime as datetime)<@bookSTime or cast(B.b_eTime as datetime)<@bookSTime group by R.rt_Id,R.rt_num,A.a_num,B.b_num
房型表(roomType): 房型编号(rt_Id),房间总数(rt_num),酒店编号(rt_hId)
线上订房表(A):标识编号(A_Id),订房数量(A_num),入住时间(A_sTime),退房时间(A_eTime),房型编号(A_rtId)
线下订房表(B):标识编号(B_Id),订房数量(B_num),入住时间(B_sTime),退房时间(B_eTime),房型编号(B_rtId)注:线上订房表与线下订房表结构是不同的,不过以上几个字段是相同的,可以用union整合在一起
用户输入酒店编号,订房数量,入住时间,退房时间 来查询该酒店所有房型的状态
只要在用户输入的入住时间与退房时间期间有一天某个房型剩余的房间数不足用户输入的订房数量,则表示该房型不可用最后得到的数据如下: