本文介绍了从两个给定的表生成结果集的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
向所有codeproject成员问好.我希望您能关注我的以下问题:
1).我有一张桌子类别:
hello to all the codeproject members. i want your kind attention towards my followings problem:
1). i have a table Category :
[CatID] int
,[cat_code] varchar(20)
,[cat_name] varchar(50)
,[total_room] int
,[cstid] int
and suppose this table having data like this
CatID cat_code cat_name total_room cstid
30 GD Grand Delux 6 168
31 SU Suite 2 168
2). I have a another table RoomInventory:
[RID] [int]
[RDate] [date],
[cat_code] [char](10),
[occupied] [int] ,
[Total] [int] ,
[available] [int],
[confirmed] [int] ,
[Tentative] [int],
[cstid] [int] NULL
and suppose this table having data like this
RID RDate cat_code occupied Total available confirmed Tentative cstid
24650 2012-03-20 GD 3 6 3 1 2 168
24651 2012-03-22 GD 1 6 5 1 0 168
24652 2012-03-23 SU 1 2 1 0 1 168
3). But i want the result like the following table if i supply the cstid e.g. 168 and startdate e.g. 2012-03-20 and enddate e.g. 2012-03-23. Note that the records marked as italic are not in the RoomInventory table
Date cat_code occupied Total available confirmed Tentative cstid
2012-03-20 GD 3 6 3 1 2 168
2012-03-20 SU 0 2 2 0 0 168
2012-03-21 GD 0 6 6 0 0 168
2012-03-21 SU 0 2 2 0 0 168
2012-03-22 GD 1 6 5 5 0 168
2012-03-22 SU 0 2 2 0 0 168
2012-03-23 GD 0 6 6 0 0 168
2012-03-23 SU 1 2 1 0 1 168
Thanks in advance
推荐答案
DECLARE @StartDate DATETIME , @EndDate DATETIME, @Date DATETIME
DECLARE @AllDates TABLE (Date DATETIME)
SET @StartDate = '2012-01-01'
SET @EndDate = '2012-01-05'
SET @Date = @StartDate
WHILE (@Date <= @EndDate)
BEGIN
INSERT INTO @AllDates
VALUES (@Date)
SET @Date = DATEADD(dd, 1, @Date)
END
SELECT ad.Date
, c.cat_code
,ISNULL(ri.occupied,0) occupied
,ISNULL(ri.Total,0) Total
,ISNULL(ri.available,0) available
,ISNULL(ri.confirmed,0) confirmed
,ISNULL(ri.Tentative,0) Tentative
,c.cstid
FROM @AllDates ad
JOIN Category c ON 1=1
LEFT JOIN RoomInventory ri ON c.cstid = ri.cstId AND c.cat_code = ri.cat_code
这篇关于从两个给定的表生成结果集的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!