利用整数表解决复杂SQL查询——案例二

问题描述：

 

Count汇聚一组行，并告诉每一组有多少行数据。但是，如果想得到相反的过程，将汇聚结果回推到多行数据时，该怎么办呢？

 

一旦得到了汇聚数据，将其分离开来就会十分棘手。例如，有一个包含宾馆房间预订的表bookings，每一行都有一个指明预订第一夜的日期、总费用以及预订天数。

 

Startwhn	Visitprice	Nights
2005-01-01	100	2
2005-02-01	200	5

 

在已知这些信息的情况下，弄清楚某个特定夜晚被预订了多少个房间是件困难的事情。我们希望处理这些信息，以便得到这样的结果：客人停留的每一夜都包含一行。Desired表的格式如下：

 

Startwhn	Whn	prices
2005-01-01	2005-01-01	50
2005-01-01	2005-01-02	50
2005-02-01	2005-02-01	40
2005-02-01	2005-02-02	40
2005-02-01	2005-02-03	40
2005-02-01	2005-02-04	40
2005-02-01	2005-02-05	40

 

很容易从desired得到booking，但这是我们需要做的事情的反过程：

 

Select startwhn,sum(price),count(price)

From desired group by startwin;

 

解决方案：

 

从booking得到desired需要更深入的思考，可是通过使用一个整数表来解决这个问题：

Integers表包含了单个列，保存了从1到某个更大整数之间的数字，在本问题的情况下，integers表至少要达到任何客人预订的最大天数。这样：

Create table integers(n int primary key);

Insert into integers values(1);

Insert into integers values(2);

Insert into integers values(3);

Insert into integers values(4);

Insert into integers values(5);

……..

或者也可以这样来实现插入数据：

Insert into integers select rownum rn from dual connect by level<=5;

 

在各种查询中integers都是一个有用的表，生成desired表的关键是将integers交叉连接到bookings上，使用条件n不大于nights：

 

SQL> select startwhn,startwhn+n-1 as whn, visitprice/nights as price

  2  from bookings,integers         

  3  where n between 1 and nights;

 

STARTWHN       WHN                 PRICE

-------------- -------------- ----------

01-1月 -05     01-1月 -05             50

01-1月 -05     02-1月 -05             50

01-2月 -05     01-2月 -05             40

01-2月 -05     02-2月 -05             40

01-2月 -05     03-2月 -05             40

01-2月 -05     04-2月 -05             40

01-2月 -05     05-2月 -05             40

 

已选择7行。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/15203236/viewspace-590898/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/15203236/viewspace-590898/