从两个给定的表生成结果集 [英] generate a resultset from two given tables

问题描述

向所有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

推荐答案

技巧是创建一个新的临时表，其中包含所有给定两个日期之间的日期.请在下面找到查询.我还没有真正创建并执行表.

Trick is to create a new temp table that holds all the dates between the given two dates. Please find the query below. I havent really created the tables and executed it.

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 

这篇关于从两个给定的表生成结果集的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！