SQL解惑(第2版) 的一些样题

newkid

谜题6　预订旅馆房间

这篇是纯转帖，里面有个实现表约束的方法很巧妙，值得学习。

说的是有一张表存储每个旅馆房间的预定情况：
CREATE TABLE Hotel
(room_nbr INTEGER NOT NULL,
arrival_date DATE NOT NULL,    ---- 入住日期
departure_date DATE NOT NULL,  ---- 离开日期
guest_name CHAR(30) NOT NULL,
PRIMARY KEY (room_nbr, arrival_date),
CHECK (departure_date >= arrival_date)
);

现在要实现的是每两条记录之间不允许出现入住时间区间的重叠。

Answer #1
这是别的数据库的方法，ORACLE中不支持。
One solution requires a product to have the capability of using fairly
complex SQL in the CHECK() clause, so you will find that a lot of
implementations will not support it.
CREATE TABLE Hotel
(room_nbr INTEGER NOT NULL,
arrival_date DATE NOT NULL,
departure_date DATE NOT NULL,
guest_name CHAR(30),
PRIMARY KEY (room_nbr, arrival_date),
CHECK (departure_date >= arrival_date),
CHECK (NOT EXISTS
(SELECT *
FROM Hotel AS H1, Hotel AS H2
WHERE H1.room_nbr = H2.room_nbr
AND H1.arrival_date
BETWEEN H2.arrival_date AND
H2.departure_date)));

Answer #2
Another solution is to redesign the table, giving a row for each day and
each room, thus:
CREATE TABLE Hotel
(room_nbr INTEGER NOT NULL,
occupy_date DATE NOT NULL,
guest_name CHAR(30) NOT NULL,
PRIMARY KEY (room_nbr, occupy_date, guest_name));
如果住10天就会有10条记录，这个不推荐。


Answer #3
Lothar Flatz, an instructor for Oracle Software Switzerland, made the
objection that the clause "H1.arrival_date BETWEEN
H2.arrival_date AND H2.depatures" does not allow for a guest name
to arrive the same day another guest name leaves.
Since Oracle cannot put subqueries into CHECK() constraints and
triggers would not be possible because of the mutating table problem, he
used a VIEW that has a WITH CHECK OPTION to enforce the occupancy
constraints:

CREATE VIEW HotelStays (room_nbr, arrival_date,
departure_date, guest_name)
AS SELECT room_nbr, arrival_date, departure_date,
guest_name
FROM Hotel AS H1
WHERE NOT EXISTS
(SELECT *
FROM Hotel AS H2
WHERE H1.room_nbr = H2.room_nbr
AND H2.arrival_date < H1.arrival_date
PUZZLE 6 HOTEL RESERVATIONS 23
AND H1.arrival_date < H2.departure_date)
WITH CHECK OPTION;

For example,
INSERT INTO HotelStays
VALUES (1, '2008-01-01', '2008-01-03', 'Coe');
COMMIT;
INSERT INTO HotelStays
VALUES (1, '2008-01-03', '2008-01-05', 'Doe');
will give a WITH CHECK OPTION clause violation on the second INSERT
INTO statement. This is a good trick for getting table-level constraints in
a table on products without full SQL-92 features.

最后一个方法是个很好的技巧，他使用了WITH CHECK OPTION的视图来实现约束。注意对表的插入和修改都必须改成对视图操作。但是读数据还是从表来，才不会无端多了个NOT EXISTS的开销。

如果不用这个方法，那就得用触发器或者在应用中写校验代码，全都是复杂而且低效的方法。

〇〇

#60的解在9i报递归SQL临时表空间（40M）不足错误

newkid

哦？9i下计划有什么不同？
把第一个WITH加上提示会怎样？
WITH w AS (SELECT /*+ materialize */ ROWNUM i FROM DUAL CONNECT BY ROWNUM<=8)

lazyman2008

太牛了，学习。

CaptainKobe

mark，找个时间慢慢领会

QIvan_lee

先copy 下来，慢慢看～

HENKEY_YANG

谢分享

简单一点_说两句

学习了#46促销，明白了
RANK() OVER(PARTITION BY promo_name ORDER BY SUM(sale_amt) DESC) RNK  --可以并列第一
row_number () over(PARTITION BY promo_name ORDER BY SUM(sale_amt) DESC) RNK --第一只有一个
不错不错，继续学习。
强大的分析函数。

durexlollipop

第一页还没看完

newkid

继续60楼的谜题67：婚姻稳定问题
我刚刚装好11GR2，就用新特性“带递归的WITH子查询”来做一下, 把它的7个子查询用递归实现：

WITH w AS (SELECT ROWNUM i FROM DUAL CONNECT BY ROWNUM<=8)
,p AS (
SELECT SUBSTR(h.man,2) AS h,SUBSTR(w.woman,2) AS w,h.ranking hr,w.ranking wr
  FROM Husbands h, Wives w
WHERE h.man=w.man and h.woman = w.woman
)
,b AS ( --- 求出所有不稳定的过滤表达式。
SELECT /*+ materialize */
       LPAD(p1.w,p1.h,'_')||LPAD(p2.w,p2.h-p1.h,'_')  bad
  FROM p p1,p p2, p p3, p p4
WHERE p1.h<p2.h  
       AND p1.w<>p2.w
       AND p1.h=p3.h AND p1.w=p4.w AND p2.h=p4.h AND p2.w=p3.w
       AND (p1.hr>p3.hr AND p2.wr>p3.wr
            OR p2.hr>p4.hr AND p1.wr>p4.wr
            )
)
,t2(a,c,lvl) AS (
    SELECT SUBSTR('12345678', i, 1) a,SUBSTR('12345678',1,i-1)||SUBSTR('12345678',i+1) c, 1 AS lvl
      FROM w
    UNION ALL
    SELECT CAST(a || SUBSTR(c , i , 1) AS VARCHAR2(10)) a,CAST(SUBSTR(c,1,i-1)||SUBSTR(c,i+1) AS VARCHAR2(10)) c,lvl+1
      FROM w,t2
     WHERE w.i<=8-t2.lvl
           AND NOT EXISTS (SELECT 1 FROM b WHERE a || SUBSTR(c , i , 1) LIKE b.bad)
           -- AND t2.lvl=(SELECT MAX(lvl) FROM t2) ---- 此处功能不支持
           AND t2.lvl<8
)
SELECT a FROM t2 WHERE lvl=8;

A
----------
74381526
24381576
24317586
63481572
64381572
36481572
63417582
64317582
36417582

9 rows selected.

这里面有个问题，每次递归时我们希望仅仅留下最外层的数据，但是我没有办法去掉其它的，这样就造成许多不必要的连接开销。