如何使用日历表简化查询

    我从 [article on Pivot Tables] 中摘出了这个例子。首先，让我们创建一个表，并插入一些数据：

    CREATE TABLE BUSINESS_TRIP(EMPLOYEE_ID INT NOT NULL,
DATE_FROM DATE NOT NULL, DATE_TO DATE NOT NULL);
    INSERT INTO BUSINESS_TRIP VALUES
    (1, DATE('01/06/2003'), DATE('01/10/2003')),
    (1, DATE('01/13/2003'), DATE('01/17/2003')),
    (1, DATE('01/20/2003'), DATE('01/24/2003')),
    (1, DATE('01/27/2003'), DATE('01/31/2003')),
    (2, DATE('01/07/2003'), DATE('01/08/2003')),
    (3, DATE('01/08/2003'), DATE('01/09/2003'));

    假设有一个简单的任务：“选择 2003 年 1 月份没有雇员出差的所有日子”，这时日历表 DATE_SEQ 就很好用了。

    SELECT SOME_DATE AS NOBODY_ON_TRIP FROM DATE_SEQ
    WHERE SOME_DATE BETWEEN DATE('01/01/2003') AND DATE('01/31/2003')
    AND NOT EXISTS(SELECT * FROM BUSINESS_TRIP WHERE SOME_DATE
 BETWEEN DATE_FROM AND DATE_TO);
    NOBODY_ON_TRIP
    -------------- 
    01/01/2003
    01/02/2003
    01/03/2003
    01/04/2003
    01/05/2003
    01/11/2003
    01/12/2003
    01/18/2003
    01/19/2003
    01/25/2003
    01/26/2003
    11 record(s) selected.

    这个查询非常简单。在[article on Pivot Tables]中讨论了一些肯定是更复杂的替代方案。

    假设有一个类似的任务：“选择 2003 年 1 月份有两名以上雇员在出差的所有日子”，同样，这里日历表 DATE_SEQ 也提供了一个非常容易的方法：

    SELECT SOME_DATE AS THREE_OR_MORE_ON_TRIP FROM DATE_SEQ
    WHERE SOME_DATE BETWEEN DATE('01/01/2003') AND DATE('01/31/2003')
    AND (SELECT COUNT(*) FROM BUSINESS_TRIP WHERE SOME_DATE
 BETWEEN DATE_FROM AND DATE_TO) > 2;
    THREE_OR_MORE_ON_TRIP
    ------------------- 
    01/08/2003

    同样，如果不能创建辅助表，我们就可以使用表表达式：

    SELECT SOME_DATE AS THREE_OR_MORE_ON_TRIP
    FROM
    (SELECT DATE('01/01/2003') + ROW_NUMBER() OVER() DAYS AS
 SOME_DATE FROM SYSCAT.TABLES) AS DATE_SEQ
    WHERE SOME_DATE BETWEEN DATE('01/01/2003') AND DATE('01/31/2003')
    AND (SELECT COUNT(*) FROM BUSINESS_TRIP WHERE SOME_DATE BETWEEN
 DATE_FROM AND DATE_TO) > 2;

    使用顺序表将两条记录布置到一行上

    假设该表的结构和数据如下：  

  CREATE TABLE VEHICLE_ACCIDENT(
    ACCIDENT_ID INT NOT NULL,
    TAG_NUMBER CHAR(10) ,
    TAG_STATE CHAR(2) );
    INSERT INTO VEHICLE_ACCIDENT VALUES(1,'123456','IL'),
(1,'234567','IL'),(1,'34567TT','WI');

    （为了简单起见，这里省略了其他列）。注意，在一起事故中可能牵涉到不止两辆车。这就要求将两条记录布置到一行上，像这样（当牵涉到 3 辆车时）：

    TAG_NUMBER_1 TAG_STATE_1 TAG_NUMBER_2 TAG_STATE_2
    ------------ ----------- ------------ -----------
    123456IL234567IL
    3456TTWI   

    通过使用 ROW_NUMBER() ，这一点很容易实现：

    WITH VEHICLE_ACCIDENT_RN(ACCIDENT_ID, ROWNUM, TAG_NUMBER, TAG_STATE) AS
    (SELECT ACCIDENT_ID, ROW_NUMBER() OVER() AS ROWNUM, TAG_NUMBER,
TAG_STATE FROM VEHICLE_ACCIDENT)
    SELECT
    LEFT_SIDE.TAG_NUMBER AS TAG_NUMBER_1,
    LEFT_SIDE.TAG_STATE AS TAG_STATE_1,
    RIGHT_SIDE.TAG_NUMBER AS TAG_NUMBER_2,
    RIGHT_SIDE.TAG_STATE AS TAG_STATE_2
    FROM
    (SELECT L.*, (L.ROWNUM+1)/2 AS PAGENUM FROM VEHICLE_ACCIDENT_RN
 L WHERE MOD(ROWNUM,2)=1)AS LEFT_SIDE
    LEFT OUTER JOIN
    (SELECT R.*, (R.ROWNUM+1)/2 AS PAGENUM FROM VEHICLE_ACCIDENT_RN
 R WHERE MOD(ROWNUM,2)=0)AS RIGHT_SIDE
    ON LEFT_SIDE.PAGENUM = RIGHT_SIDE.PAGENUM
    WHERE LEFT_SIDE.ACCIDENT_ID=1 AND (RIGHT_SIDE.ACCIDENT_ID=1
 OR RIGHT_SIDE.ACCIDENT_ID IS NULL)

    不管事故涉及的车辆是奇数还是偶数，该查询都给出了正确的结果。可以随意添加记录并进行检验。同样，如上一章所述，也可以通过一些其他的方法来解决这个问题。通过使用 ROW_NUMBER() ，我们可以得到一个很简单的解决方案，这个方案可以快捷地开发出来，并且易于理解。