如何使用sql查询生成房间状态 [英] How to i generate room status using sql query
问题描述
我有这样的桌面结构
id int
checkinttime datetime
checkouttime datetime
roomid int
我在此表中记录了登记和结账时间。我的问题是如何生成一个日期报告,生成该日期范围内是否占用空间。报告应包含所选的所有房间,并选择开始日期和结束日期。
room 3/1/2016 3/2/2016 3/3/2016 6 / 4/2016
r1 PVVP
r2 vppp
其中p表示已占用,V表示空置。如何为此写SQL。?
提前谢谢
我试过的:
我凭借自己的知识尝试过。但它不起作用。我的sql知识是有限的。
看看
使用PIVOT和UNPIVOT [ ^ ]
在SQL查询中使用Pivot的简单方法 [ ^ ]
Sql Server中的PIVOT和UNPIVOT | SqlHints.com [ ^ ]
I am having a table structure like this
id int
checkinttime datetime
checkouttime datetime
roomid int
I have recorded checkin and checkout time in this table. My question is how to generate a datewise report that generate whether room occupied or not in that date range like this. the report should contains all rooms group wise with the selected start date and end date.
room 3/1/2016 3/2/2016 3/3/2016 6/4/2016
r1 P V V P
r2 v p p p
where p means Occupied, and V means vacant. How to write SQL for this.?
Thanks in advance
What I have tried:
I tried with my knowledge. But its not working. My sql knowledge is limited.
Have a look at
Using PIVOT and UNPIVOT[^]
Simple Way To Use Pivot In SQL Query[^]
PIVOT and UNPIVOT in Sql Server | SqlHints.com[^]
这篇关于如何使用sql查询生成房间状态的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!