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

newkid

样题由OO提供情报，来自 http://book.csdn.net/bookfiles/665/

以下是我补充的一些ORACLE写法，不一定比原写法更佳。

谜题18　广告信件

给你一个客户地址表，我们希望向他们邮寄广告信件。这个表有一个家庭（fam）列，把家庭住址相同的客户（con_id）联系起来。这样做的原因是按照规则要求向每个家庭只邮寄一份。这个列包含第一个具有这个地址的人的PRIMARY KEY值。表大致如下：

Consumers

con_name     address  con_id  fam

==================================

'Bob'         'A'       1    NULL

'Joe'         'B'       3    NULL

'Mark'        'C'       5    NULL

'Mary'        'A'       2    1

'Vickie'      'B'       4    3

'Wayne'       'D'       6    NULL

需要删除那些fam为NULL、但其他家庭成员在邮寄列表中的行。在上面的示例中，需要删除Bob和Joe，但不删除Mark和Wayne。

解惑 #1

最初的尝试可能是试图去做很多工作，但是将文字说明直接转换成SQL将产生以下结果：

DELETE FROM Consumers

WHERE fam IS NULL  -- this guy has a NULL family value

   AND EXISTS  -- ..and there is someone who is

       (SELECT *

          FROM Consumers AS C1

         WHERE C1.con_id <> Consumers.con_id -- a different person

           AND C1.address = Consumers.address -- at same address

           AND C1.fam IS NOT NULL); -- who has a family value

解惑 #2

但是如果思考一下，会发现每个家庭的COUNT(*)必然大于1。

DELETE FROM Consumers

WHERE fam IS NULL    --   this guy has a NULL family value

   AND (SELECT COUNT(*)

          FROM Consumers AS C1

         WHERE C1.address = Consumers.address) > 1;

技巧是COUNT(*)聚集将在计算中包括NULL。

解惑 #3

解惑1的另一个版本来自Franco Moreno：

DELETE FROM Consumers

WHERE fam IS NULL  -- this guy has a NULL family value

   AND EXISTS (SELECT *

                 FROM Consumers AS C1

书中#2和#1并不等价，如果Mary的fam为NULL,则#1不删除而#2会删除。除非把COUNT(*)换成COUNT(fam)就和#1等价了。
#3写法原页面没有完整给出。

补充ORACLE写法：

CREATE TABLE Consumers (con_name VARCHAR2(20), address VARCHAR2(20), con_id NUMBER,  fam NUMBER);

INSERT INTO consumers VALUES ('Bob'    ,'A', 1 , NULL);
INSERT INTO consumers VALUES ('Joe'    ,'B', 3 , NULL);
INSERT INTO consumers VALUES ('Mark'   ,'C', 5 , NULL);
INSERT INTO consumers VALUES ('Mary'   ,'A', 2 , 1   );
INSERT INTO consumers VALUES ('Vickie' ,'B', 4 , 3   );
INSERT INTO consumers VALUES ('Wayne'  ,'D', 6 , NULL);

DELETE FROM Consumers
WHERE con_id IN (
       SELECT con_id
        FROM (SELECT c.*
                    ,COUNT(fam) OVER(PARTITION BY address) cnt
                FROM Consumers c
              )
       WHERE fam IS NULL AND cnt>0
       );

比较另类的用MERGE做删除写法（10G以上）
MERGE INTO Consumers c
USING (SELECT con_id
        FROM (SELECT c.*
                    ,COUNT(fam) OVER(PARTITION BY address) cnt
                FROM Consumers c
              )
       WHERE fam IS NULL AND cnt>0
       ) n
ON (c.con_id = n.con_id)
WHEN MATCHED THEN UPDATE SET fam=NULL
DELETE WHERE 1=1;

[ 本帖最后由 newkid 于 2010-3-12 00:22 编辑 ]

newkid

谜题41　预算

佛罗里达州迈阿密市LanSoft公司的Mark Frontera于1995年9月发表了这个问题。他的预算信息由下面3个表组成：需要付款的物品、购买这些物品的预计费用和实际费用。这些表都很简单，这里略过DDL，只列出数据。

注意一笔货款可以由几张支票支付，一张支票有时也会支付几笔货款。

Items

item_nbr   item_descr

======================

  10       'item 10'

  20       'item 20'

  30       'item 30'

  40       'item 40'

  50       'item 50'

Actuals

item_nbr  actual_amt  check_nbr

===============================

  10      300.00       '1111'

  20      325.00       '2222'

  20      100.00       '3333'

  30      525.00       '1111'

Estimates

item_nbr  estimated_amt

=======================

  10        300.00

  10         50.00

  20        325.00

  20        110.00

  40         25.00

希望从一条查询中得到下面的输出：

结果

item_nbr    item_descr    actual_tot    estimate_tot    check_nbr

=================================================================

  10        'item 10'        300.00        350.00        '1111'

  20        'item 20'        425.00        435.00        'Mixed'

  30        'item 30'        525.00          NULL        '1111'

  40        'item 40'          NULL         25.00         NULL

因为在Actuals和Estimates表中都没有Items表中物品50的记录，所以这里不显示它。Actual_tot列是该物品实际花费的总金额，estimate_tot列是该物品预计花费的总金额。

       

解惑 #1


我认为这个模式需要做些修改，不过可以使用标量子查询和一些富有技巧的代码来完成：

SELECT item_nbr, item_descr, actual_tot, estimate_tot, check_nbr

  FROM (SELECT I1.item_nbr, I1.item_descr,

            (SELECT SUM (A1.actual_amt)

               FROM Actuals AS A1

              WHERE I1.item_nbr = A1.item_nbr),

            (SELECT SUM (E1.estimated_amt)

               FROM Estimates AS E1

              WHERE I1.item_nbr = E1.item_nbr),

            (SELECT CASE WHEN COUNT(*) = 1

                    THEN MAX(check_nbr)

                    ELSE 'Mixed' END

               FROM Actuals AS A2

              WHERE I1.item_nbr = A2.item_nbr

              GROUP BY item_nbr)

        FROM Items AS I1)

        AS X (item_nbr, item_descr, actual_tot, estimate_tot, check_nbr)

  WHERE X.actual_tot IS NOT NULL

     OR X.estimate_tot IS NOT NULL;

技巧在标量子查询中。前两个标量子查询计算实际花费总金额和预计花费总金额，就好像它们是GROUP BY和LEFT OUTER JOIN的一部分。

最后一个子查询技巧性更高。这个查询得到我们在结果表中考虑的所有物品的实际花费，并对它们分组。如果组是空的（没有签发支票），那么子查询返回一个NULL，我们也显示NULL。如果这个组中有一张支票，那么CASE表达式将返回这个唯一的支票号。MAX()函数是一种安全上的检查，确保从子查询返回的是标量结果。并非所有的SQL-92实现都需要它。如果对某个物品签发了多张支票，则COUNT(*)大于1，得到的是字符串是'Mixed'而不是代表唯一支票号的字符串。

解惑 #2

可以使用LEFT OUTER JOIN代替子查询：

SELECT I1.item_nbr, I1.item_descr,

       SUM(A1.actual_amt) AS tot_act,

       SUM(E1.estimated_amt) AS estimate_tot,

       (SELECT CASE WHEN COUNT(check_nbr) = 0

               THEN NULL

               WHEN COUNT(check_nbr) = 1

               THEN MAX(check_nbr)

               ELSE 'Mixed' END

          FROM Actuals A2

         WHERE A2.item_nbr = I1.item_nbr) AS check_nbr

  FROM (Items AS I1

        LEFT OUTER JOIN

       (SELECT item_nbr,

               SUM(actual_amt) AS actual_amt

          FROM Actuals

         GROUP BY item_nbr) AS A1

            ON I1.item_nbr = A1.item_nbr)

               LEFT OUTER JOIN

               (SELECT item_nbr,

                       SUM(estimated_amt) AS estimated_amt

                       FROM Estimates

                 GROUP BY item_nbr) AS E1

    ON I1.item_nbr = E1.item_nbr

GROUP BY I1.item_nbr, I1.item_descr;

本来#2是不错的，但是作者有些犯浑，最后弄了个不必要的GROUP BY,还把check_nbr用不必要的SCALAR SUBQUERY实现了。
我认为更好的写法：

CREATE TABLE Items (item_nbr NUMBER,   item_descr VARCHAR2(20));

INSERT INTO items VALUES(10,'item 10');
INSERT INTO items VALUES(20,'item 20');
INSERT INTO items VALUES(30,'item 30');
INSERT INTO items VALUES(40,'item 40');
INSERT INTO items VALUES(50,'item 50');

CREATE TABLE Actuals (item_nbr NUMBER, actual_amt NUMBER, check_nbr VARCHAR2(20));

INSERT INTO actuals VALUES (10, 300.00 ,'1111');
INSERT INTO actuals VALUES (20, 325.00 ,'2222');
INSERT INTO actuals VALUES (20, 100.00 ,'3333');
INSERT INTO actuals VALUES (30, 525.00 ,'1111');

CREATE TABLE Estimates (item_nbr NUMBER, estimated_amt NUMBER);

INSERT INTO estimates VALUES (10, 300.00);
INSERT INTO estimates VALUES (10,  50.00);
INSERT INTO estimates VALUES (20, 325.00);
INSERT INTO estimates VALUES (20, 110.00);
INSERT INTO estimates VALUES (40,  25.00);

COMMIT;


SELECT I1.item_nbr
      ,I1.item_descr
      ,A1.actual_amt AS tot_act
      ,E1.estimated_amt AS estimate_tot
      ,A1.check_nbr
  FROM (Items I1
        LEFT OUTER JOIN
       (SELECT item_nbr,
               SUM(actual_amt) AS actual_amt
              ,(CASE WHEN COUNT(check_nbr) = 0
                     THEN NULL
                     WHEN COUNT(check_nbr) = 1
                     THEN MAX(check_nbr)
                     ELSE 'Mixed'
                END)  check_nbr
          FROM Actuals
         GROUP BY item_nbr)  A1
            ON I1.item_nbr = A1.item_nbr)
               LEFT OUTER JOIN
               (SELECT item_nbr,
                       SUM(estimated_amt) AS estimated_amt
                       FROM Estimates
                 GROUP BY item_nbr)  E1
    ON I1.item_nbr = E1.item_nbr
ORDER BY 1;

  ITEM_NBR ITEM_DESCR            TOT_ACT ESTIMATE_TOT CHECK_NBR
---------- ------------------ ---------- ------------ -----------
        10 item 10                   300          350 1111
        20 item 20                   425          435 Mixed
        30 item 30                   525              1111
        40 item 40                                 25
        50 item 50

如果最后一行的50不要，只需加上 WHERE A1.item_nbr IS NOT NULL OR E1.item_nbr

newkid

谜题26　数 据 流 图

Tom Bragg在CompuServe上的CASE论坛中发表过这个问题的另一个版本。有一个数据流图（DFD）的表，这个表包括图名、每个图中气泡的名称和流程线上的标签名。表是这样的：

CREATE TABLE DataFlowDiagrams
(diagram_name CHAR(10) NOT NULL,
bubble_name CHAR(10) NOT NULL,
flow_name CHAR(10) NOT NULL,
PRIMARY KEY (diagram_name, bubble_name, flow_name));

为了说明问题，使用下面的表：

DataFlowDiagrams

diagram_name  bubble_name  flow_name
====================================
   Proc1         input      guesses
   Proc1         input      opinions
   Proc1         crunch     facts
   Proc1         crunch     guesses
   Proc1         crunch     opinions
   Proc1         output     facts
   Proc1         output     guesses
   Proc2         reckon     guesses
   Proc2         reckon     opinions
   ...

我们需要查找的是：哪些流程没有进入到图中的气泡中。这将成为搜索丢失数据流的图形验证例程的一部分。为了使问题容易，假设所有的气泡都应该有所有流程。这意味着(Proc1, input)缺少了'facts'流程，(Proc1, output)缺少了'opinions'流程。

解惑 #1

可以使用下面的SQL-92查询：

SELECT F1.diagram_name, F1.bubble_name, F2.flow_name

  FROM (SELECT F1.diagram_name, F1.bubble_name

          FROM DataFlowDiagrams AS F1

         CROSS JOIN

        SELECT DISTINCT F2.flow_name

          FROM DataFlowDiagrams AS F2)

        EXCEPT

        SELECT F3.diagram_name, F3.bubble_name, F3.flow_name

          FROM DataFlowDiagrams AS F3;

它采用的根本方法是：产生所有可能的图形和流程的组合，然后删除已经有的那些。

解惑 #2

另一个SQL-92查询是：

SELECT F1.diagram_name, F1.bubble_name, F2.flow_name

  FROM (SELECT F1.diagram_name, F1.bubble_name

          FROM DataFlowDiagrams AS F1

         CROSS JOIN

        SELECT DISTINCT F2.flow_name

          FROM DataFlowDiagrams AS F2)

         WHERE F2.flow_name NOT IN (SELECT F3.flow_name

                              FROM DataFlowDiagrams AS F3

                             WHERE F3.diagram_name = F1.diagram_name

                               AND F3.bubble_name = F1.bubble_name)

ORDER BY F1.diagram_name, F1.bubble_name, F2.flow_name;

解惑 #3

或者在SQL-89中解答这个谜题，需要使用VIEW：

CREATE VIEW AllDFDFlows (flow_name)

AS SELECT DISTINCT flow_name FROM DataFlowDiagrams;

-- attach all the flows to each row of the original table

CREATE VIEW NewDFD (diagram_name, bubble_name, flow_name, missingflow)

AS SELECT DISTINCT F1.diagram_name, F1.bubble_name, F1.flow_name, F2.flow_name

      FROM DataFlowDiagrams AS F1, AllDFDFlows AS F2

     WHERE F1.flow_name <> F2.flow_name;

-- Show me the (diagram_name, bubble_name) pairs and missing flow

-- where the missing flow was not somewhere in the flow column

-- of the pair.

SELECT DISTINCT diagram_name, bubble_name, missingflow

  FROM NewDFD AS ND1

WHERE NOT EXISTS (SELECT *

                     FROM NewDFD AS ND2

                    WHERE ND1.diagram_name = ND2.diagram_name

                      AND ND1.bubble_name = ND2.bubble_name

                      AND ND2.flow_name = ND1.missingflow)

ORDER BY diagram_name, bubble_name, missingflow;

DISTINCT可能用得太多了，但是你可以做个实验，看一下执行速度。这样做比将所有行都在网络上移动一次要快。

CREATE TABLE DataFlowDiagrams
(diagram_name CHAR(10) NOT NULL,
bubble_name CHAR(10) NOT NULL,
flow_name CHAR(10) NOT NULL,
PRIMARY KEY (diagram_name, bubble_name, flow_name));

INSERT INTO dataflowdiagrams VALUES ('Proc1','input'  ,'guesses' );
INSERT INTO dataflowdiagrams VALUES ('Proc1','input'  ,'opinions');
INSERT INTO dataflowdiagrams VALUES ('Proc1','crunch' ,'facts'   );
INSERT INTO dataflowdiagrams VALUES ('Proc1','crunch' ,'guesses' );
INSERT INTO dataflowdiagrams VALUES ('Proc1','crunch' ,'opinions');
INSERT INTO dataflowdiagrams VALUES ('Proc1','output' ,'facts'   );
INSERT INTO dataflowdiagrams VALUES ('Proc1','output' ,'guesses' );
INSERT INTO dataflowdiagrams VALUES ('Proc2','reckon' ,'guesses' );
INSERT INTO dataflowdiagrams VALUES ('Proc2','reckon' ,'opinions');

#1和#2都有改进余地，
SELECT F1.diagram_name, F1.bubble_name
  FROM DataFlowDiagrams AS F1
应该为:
SELECT DISTINCT F1.diagram_name, F1.bubble_name
  FROM DataFlowDiagrams AS F1
加上DISTINCT可以减少CROSS JOIN的结果数。

#3我个人不喜欢。

#1的ORACLE写法：
SELECT diagram_name, bubble_name, flow_name
  FROM (SELECT DISTINCT diagram_name, bubble_name
          FROM DataFlowDiagrams)  F1
         CROSS JOIN
        (SELECT DISTINCT flow_name
           FROM DataFlowDiagrams)  F2
        MINUS
        SELECT F3.diagram_name, F3.bubble_name, F3.flow_name
          FROM DataFlowDiagrams  F3

#2的ORACLE写法：
SELECT diagram_name, bubble_name, flow_name
  FROM (SELECT DISTINCT diagram_name, bubble_name
          FROM DataFlowDiagrams)  F1
         CROSS JOIN
        (SELECT DISTINCT flow_name
          FROM DataFlowDiagrams)  F2
         WHERE flow_name NOT IN (SELECT F3.flow_name
                              FROM DataFlowDiagrams  F3
                             WHERE F3.diagram_name = F1.diagram_name
                               AND F3.bubble_name = F1.bubble_name)


把#2的NOT IN 改为外连接：

SELECT F1.diagram_name, F1.bubble_name, F2.flow_name
  FROM (SELECT DISTINCT diagram_name, bubble_name
          FROM DataFlowDiagrams)  F1
         CROSS JOIN
        (SELECT DISTINCT flow_name
          FROM DataFlowDiagrams)  F2
         LEFT JOIN
       DataFlowDiagrams  F3
       ON F3.diagram_name = F1.diagram_name
          AND F3.bubble_name = F1.bubble_name
          AND F3.flow_name = F2.flow_name
WHERE F3.flow_name IS NULL;

类似的还有NOT EXISTS写法：
SELECT F1.diagram_name, F1.bubble_name, F2.flow_name
  FROM (SELECT DISTINCT diagram_name, bubble_name
          FROM DataFlowDiagrams)  F1
         CROSS JOIN
        (SELECT DISTINCT flow_name
          FROM DataFlowDiagrams)  F2
WHERE NOT EXISTS (SELECT NULL
                     FROM DataFlowDiagrams  F3
                    WHERE F3.diagram_name = F1.diagram_name
                          AND F3.bubble_name = F1.bubble_name
                          AND F3.flow_name = F2.flow_name
                   );
                  
10G的分区外连接可以省去CROSS JOIN:

SELECT F1.diagram_name, F1.bubble_name, F2.flow_name
  FROM DataFlowDiagrams F1
       PARTITION BY (diagram_name, bubble_name)
       RIGHT JOIN (SELECT DISTINCT flow_name FROM DataFlowDiagrams)  F2
       ON (F1.flow_name = F2.flow_name)
WHERE F1.flow_name IS NULL;

newkid

谜题60　条码

在www.sswug.org最近发表的帖子中，一个定期撰写文章的作者发表了一个T-SQL函数，计算标准的13位条码的检查和。算法是一个简单的加权求和法（如果不知道它的含义，可以查阅Data & Databases一书的15.3.1节）。给定一个13位的数字字符串，取出这个条码字符串的前12位数字，用公式计算，看结果是否等于第13位。规则很简单：

1. 将每个奇数位的数字相加得到S1。

2. 将每个偶数位的数字相加得到S2。

用S1减去S2，将和对10取模，然后计算绝对值。计算条码检查和的公式为ABS(MOD(S1-S2), 10)。

这里是作者提出的函数代码，从T-SQL转换为标准SQL/PSM：

CREATE FUNCTION Barcode_CheckSum(IN my_barcode CHAR(12))

RETURNS INTEGER

BEGIN

DECLARE barcode_checkers INTEGER;

DECLARE idx INTEGER;

DECLARE sgn INTEGER;

SET barcode_checkers = 0;

-- check if given barcode is numeric

IF IsNumeric(my_barcode) = 0

THEN RETURN -1;

END IF;

-- check barcode length

IF CHAR_LENGTH(TRIM(BOTH ' ' FROM my_barcode))<> 12

THEN RETURN -2;

END IF;

-- compute barcode checksum algorithm

SET idx = 1;

WHILE idx <= 12

DO -- Calculate sign of digit

  IF MOD(idx, 2) = 0

  THEN SET sgn = -1;

  ELSE SET sgn = +1;

  END IF;

  SET barcode_checkers = barcode_checkers +

    CAST(SUBSTRING(my_barcode FROM idx FOR 1) AS INTEGER) * sgn;

  SET idx = idx + 1;

  END WHILE;

-- check digit

  RETURN ABS(MOD(barcode_checkers, 10));

END;

看一下它是如何工作的：

barcode_checkSum('283723281122')

= ABS (MOD(2-8 + 3-7 + 2-3 + 2-8 + 1-1 + 2-2), 10))

= ABS (MOD(-6 -4- 1 -6 +0 +0), 10)

= ABS (MOD(-17, 10))

= ABS(-7) = 7

       

解惑 #1


从哪里开始呢？注意这段代码创建了不必要的本地变量，主观假设地使用了T-SQL方言中的IsNumeric()函数，但事实上条码中的检查应该是条码中的一个字符、而不是从条码中分离出来的整数。在代码中有3个IF语句和WHILE循环。这与过程语言差不多。

说句实话，SQL/PSM不能够利用返回负值的方法处理错误，它与T-SQL中使用的机制差别很大，在这里我不想做过多描述。

为什么要使用过程代码呢？其中大部分都是可以被说明性表达式所代替的。首先在循环和嵌套函数调用的地方用常见的Sequence辅助表代替，并使用CASE表达式删除IF语句。

转换的伪公式大致为：

  ●  过程循环函数变成顺序集合：

FOR seq FROM 1 TO n DO f(seq);

   => SELECT f(seq) FROM Sequence WHERE seq <= n;

  ●  过程化的选择函数变成CASE表达式：

IF.. THEN .. ELSE

   => CASE WHEN.. THEN .. ELSE.. END;

  ●  一系列赋值和函数调用变成一个函数调用的嵌套集合：

DECLARE x <type>;

SET x = f(..);

SET y = g(x);

..;

   => f(g(x))

解惑 #2

利用上述规则初步做一些尝试，将代码改写如下：

CREATE FUNCTION Barcode_CheckSum(IN my_barcode CHAR(12))

RETURNS INTEGER

BEGIN

IF my_barcode SIMILAR TO '%[^0-9]%'

THEN RETURN -1;

ELSE RETURN

(SELECT ABS(MOD(SUM((CAST(SUBSTRING(my_barcode FROM S.seq FOR 1) AS INTEGER)

         * CASE MOD(S.seq, 2) WHEN 0 THEN 1 ELSE -1 END)), 10))

    FROM Sequence AS S

   WHERE S.seq <= 12);

END IF;

END;

记住超长的字符串不适合于参数并产生溢出错误，而一个短字符串将由空白填充。

解惑 #3

但是等等！我们可以做得更好：

CREATE FUNCTION Barcode_CheckSum(IN my_barcode CHAR(12))

RETURNS INTEGER

RETURN

(SELECT ABS(MOD((SUM((CAST (SUBSTRING(my_barcode FROM S.seq FOR 1) AS INTEGER)

         * CASE MOD(S.seq,2) WHEN 0 THEN 1 ELSE -1 END)), 10))

    FROM Sequence AS S

   WHERE S.seq <= 12

     AND my_barcode NOT SIMILAR TO '%[^0-9]%');

如果条码不对，将返回NULL。SIMILAR TO正则表达式是一个值得一提的技巧。它的双重否定确保了在所有12个位置的字符都是数值。只有一个SQL语句，所以我们做得还不错。但是有一些小的调整：

CREATE FUNCTION Barcode_CheckSum(IN my_barcode CHAR(12))

RETURNS INTEGER

RETURN

(SELECT ABS(MOD((SUM(CAST(SUBSTRING(my_barcode FROM Weights.seq FOR 1) AS INTEGER)

         * Weights.wgt), 10))

    FROM (VALUES (CAST(1 AS INTEGER), CAST(-1 AS INTEGER)),

         (2, +1), (3, -1), (4, +1), (5, -1),(6, +1), (7, -1),

         (8, +1), (9, -1), (10, +1), (11, -1), (12, +1)) AS weights(seq, wgt)

   WHERE my_barcode NOT SIMILAR TO '%[^0-9]%');

标准SQL中的另一个技巧是使用VALUES()表达式构造表常量。表表达式中的第一行通过显式转换构造列的数据类型。

解惑 #4

哪个是最佳解决方法？真正的回答是，上面的都不是。这个练习的主旨是提出一个面向集合、说明性的解答。我们一直在编写函数来检查条件。我们需要的是条码的CHECK()约束。尝试使用下面的代码代替：

CREATE TABLE Products

(

..

barcode CHAR(13) NOT NULL

CONSTRAINT all_numeric_checkdigit

CHECK (barcode NOT SIMILAR TO '%[^0-9]%')

CONSTRAINT valid_checkdigit

CHECK (

        (SELECT ABS(MOD((SUM(CAST(SUBSTRING(barcode

                   FROM Weights.seq FOR 1) AS INTEGER) * Weights.wgt), 10))

           FROM (VALUES (CAST(1 AS INTEGER), CAST(-1 AS INTEGER)),

                (2, +1), (3, -1), (4, +1), (5, -1),(6, +1), (7, -1),

                (8, +1), (9, -1), (10, +1), (11, -1), (12, +1)) AS weights(seq, wgt))

                = CAST(SUBSTRING(barcode FROM 13 FOR 1) AS INTEGER))

..

);

这将把不合要求的数据从模式中排除出去，这是函数无法做到的。能够做的最接近的事情就是在插入数据时激活触发器。将代码分成两个约束的理由是可以提供更好的错误消息。这就是我们在S

又是页面不完整，搞不懂作者最后想表达什么。如果仅仅是计算CHECKSUM, ORACLE中的典型做法：

VAR V_CODE VARCHAR2(30);
EXEC :V_CODE := '283723281122';

SELECT ABS(MOD(SUM(DECODE(MOD(RN,2),1,VAL))-SUM(DECODE(MOD(RN,2),0,VAL)),10))
  FROM (SELECT ROWNUM RN
              ,SUBSTR(:V_CODE,ROWNUM,1) AS VAL
          FROM DUAL
        CONNECT BY ROWNUM<=LENGTH(:V_CODE)
        );

两个SUM可以合并：
SELECT ABS(MOD(SUM(DECODE(MOD(RN,2),1,VAL,-VAL)),10))
  FROM (SELECT ROWNUM RN
              ,SUBSTR(:V_CODE,ROWNUM,1) AS VAL
          FROM DUAL
        CONNECT BY ROWNUM<=LENGTH(:V_CODE)
        );

[ 本帖最后由 newkid 于 2010-3-13 02:22 编辑 ]

newkid

谜题46　促销

你刚得到一份工作，成为一家百货商店的销售经理。你的数据库中有两个表。一个是商店促销事件的日历，另一个是在促销期间的销售额列表。你需要编写一个查询，告诉我们在每次促销中哪位职员的销售额最高，这样可以给那个职员发绩效奖金。

CREATE TABLE Promotions

(promo_name CHAR(25) NOT NULL PRIMARY KEY,

start_date DATE NOT NULL,

end_date DATE NOT NULL,

CHECK (start_date <= end_date));

Promotions

promo_name                 start date        end date

=======================================================

'Feast of St. Fred'       '1995-02-01'      '1995-02-07'  

'National Pickle Pageant' '1995-11-01'      '1995-11-07'

'Christmas Week'          '1995-12-18'      '1995-12-25'

CREATE TABLE Sales

(ticket_nbr INTEGER NOT NULL PRIMARY KEY,

clerk_name CHAR (15) NOT NULL,

sale_date DATE NOT NULL,

sale_amt DECIMAL (8,2) NOT NULL);

解惑 #1

这个查询中的技巧是需要找出每个雇员在每次促销期间的销售额，然后从这些组中找出最高的销售总额。第一部分是一个相当容易的JOIN和GROUP BY语句。

最后一步是在每个分组中找出最高的销售总额，这需要一个颇具技巧的HAVING子句。让我们先看一下解答，然后再解释它：

SELECT S1.clerk_name, P1.promo_name, SUM(S1.sale_amt) AS sales_tot

  FROM Sales AS S1, Promotions AS P1

WHERE S1.sale_date BETWEEN P1.start_date AND P1.end_date

GROUP BY S1.clerk_name, P1.promo_name

HAVING SUM(sale_amt) >=

       ALL (SELECT SUM(sale_amt)

              FROM Sales AS S2

             WHERE S2.clerk_name <> S1.clerk_name

               AND S2.sale_date

                   BETWEEN (SELECT start_date

                              FROM Promotions AS P2

                             WHERE P2.promo_name = P1. promo_name)

                       AND (SELECT end_date

                              FROM Promotions AS P3

                             WHERE P3.promo_name = P1.promo_name)

             GROUP BY S2.clerk_name);

我们需要找出在每次促销期间，其销售总额大于等于所有其他职员销售额的职员及促销事件。谓词S2.clerk_name <> S1.clerk_name将其他职员从子查询合计中排除出去。BETWEEN谓词中的子查询表达式确保我们使用了正确的促销日期。

在试图改进这个查询时，首先想到用直接外部引用代替BETWEEN谓词中的子查询表达式，如下：

SELECT S1.clerk_name, P1.promo_name, SUM(S1.sale_amt) AS sales_tot

  FROM Sales AS S1, Promotions AS P1

WHERE S1.sale_date BETWEEN P1.start_date AND P1.end_date

GROUP BY S1.clerk_name, P1.promo_name

HAVING SUM(sale_amt) >=

ALL (SELECT SUM(sale_amt)

        FROM Sales AS S2

       WHERE S2.clerk_name <> S1.clerk_name

         AND S2.sale_date          -- Error !!

             BETWEEN P1.start_date AND P1.end_date

       GROUP BY S2.clerk_name);

但是这样做是不行的——如果你知道为什么，就算真正理解SQL了。遮住这页后面的内容，先想一想再往下读。

解惑 #2

“GROUP BY S1.clerk_name, P1.promo_name”子句创建了一个分组表，其中的行只包含聚集函数和两个分组列。在FROM子句中构造的原始工作表停止并退出，由这个分组工作表代替，这样start_date和end_date也在此时停止并退出。

但是，因为子查询表达式在外部表P1仍旧有效时引用的，所以它能够执行。因为查询是从最里面的子查询向外进行，而不是从分组表开始的。

如果我们要查找的是两个已知的日期常量之间的销售绩效，则当我们将P1.start_ date和P1.end_date替换为这两个常量后，第二个查询就可以执行了。

我的专栏的两位读者发来了这个谜题的改进版本。Richard Romley和J. D. McDonald都注意到：如果我们假设促销都不重叠，则Promotions表只有一个主键列，这样在GROUP BY子句中使用（promo_name, start_date, end_date）将不会改变分组。但是，它将使HAVING子句可以使用start_date和end_date，如下：

SELECT S1.clerk_name, P1.promo_name, SUM(S1.sale_amt) AS sales_tot

  FROM Sales AS S1, Promotions AS P1

WHERE S1.sale_date BETWEEN P1.start_date AND P1.end_date

GROUP BY P1.promo_name, P1.start_date , P1.end_date, S1.clerk_name

HAVING SUM(S1.sale_amt) >

   ALL (SELECT SUM(S2.sale_amt)

          FROM Sales AS S2

         WHERE S2.sale_date BETWEEN P1.start_date AND P1.end_date

           AND S2.clerk_name <> S1.clerk_name

         GROUP BY S2.clerk_name);

作为替代方案，在子查询中做一些简单修改可以减少HAVING子句中谓词的数目，如下：

...

HAVING SUM(S1.sale_amt) >=

       ALL (SELECT SUM(S2.sale_amt)

              FROM Sales AS S2

             WHERE S2.sale_date BETWEEN P1.start_date AND P1.end_date

             GROUP BY S2.clerk_name);

我不能确定这两种方案在性能上是否有很大差异，但是第二种方法要简洁一些。

解惑 #3

新的常用表表达式（CTE）使得在多个层次上聚集数据更容易些：

WITH ClerksTotals (clerk_name, promo_name, sales_tot) AS

(SELECT S1.clerk_name, P1.promo_name, SUM(S1.sale_amt)

   FROM Sales AS S1, Promotions AS P1

  WHERE S1.sale_date BETWEEN P1.start_date AND P1.end_date

  GROUP BY S1.clerk_name, P1.promo_name)

SELECT C1.clerk_name, C1.promo_name, C1.sales_tot

   FROM ClerksTotals AS C1

  WHERE C1.sales_tot

        = (SELECT MAX(C2.sales_tot)

             FROM ClerksTotals AS C2

            WHERE C1.promo_name = C2.promo_name);

这段代码相当紧凑，并且应该是比较容易维护的。

这个题目相当容易：

CREATE TABLE Promotions
(promo_name CHAR(25) NOT NULL PRIMARY KEY,
start_date DATE NOT NULL,
end_date DATE NOT NULL,
CHECK (start_date <= end_date));


INSERT INTO promotions VALUES ('Feast of St. Fred'       ,DATE '1995-02-01', DATE '1995-02-07' );
INSERT INTO promotions VALUES ('National Pickle Pageant' ,DATE '1995-11-01', DATE '1995-11-07' );
INSERT INTO promotions VALUES ('Christmas Week'          ,DATE '1995-12-18', DATE '1995-12-25' );

CREATE TABLE Sales
(ticket_nbr INTEGER NOT NULL PRIMARY KEY,
clerk_name CHAR (15) NOT NULL,
sale_date DATE NOT NULL,
sale_amt DECIMAL (8,2) NOT NULL);

INSERT INTO sales VALUES (1,'John',  DATE '1995-2-3', 200);
INSERT INTO sales VALUES (2,'John',  DATE '1995-2-5', 340);
INSERT INTO sales VALUES (3,'Mary',  DATE '1995-2-4', 260);
INSERT INTO sales VALUES (4,'Mary',  DATE '1995-2-6', 280);
INSERT INTO sales VALUES (5,'Tom',   DATE '1995-2-1', 400);
INSERT INTO sales VALUES (6,'John',  DATE '1995-11-3',100);
INSERT INTO sales VALUES (7,'Tom',   DATE '1995-11-3',200);
INSERT INTO sales VALUES (8,'Mary',  DATE '1995-11-4',400);
INSERT INTO sales VALUES (9,'John',  DATE '1995-11-5',200);
INSERT INTO sales VALUES (10,'Tom',  DATE '1995-11-5',100);

SELECT * FROM (
SELECT clerk_name,promo_name,SUM(sale_amt) AS sales_tot,RANK() OVER (PARTITION BY promo_name ORDER BY SUM(sale_amt) DESC) RNK
  FROM promotions,Sales
WHERE sale_date BETWEEN start_date AND end_date
GROUP BY clerk_name,promo_name
)
WHERE RNK=1;

newkid

谜题59　合并时间段

假设有一张考勤单，经常需要将连续或重叠的时间段合并。在做简单查询时这可能成为问题，所以要当心，这个问题并不容易领会或理解：

CREATE TABLE Timesheets
(task_id CHAR(10) NOT NULL PRIMARY KEY,
start_date DATE NOT NULL,
end_date DATE NOT NULL,
CHECK(start_date <= end_date));

INSERT INTO Timesheets
VALUES (1,  '1997-01-01', '1997-01-03'),
       (2,  '1997-01-02', '1997-01-04'),
       (3,  '1997-01-04', '1997-01-05'),
       (4,  '1997-01-06', '1997-01-09'),
       (5,  '1997-01-09', '1997-01-09'),
       (6,  '1997-01-09', '1997-01-09'),
       (7,  '1997-01-12', '1997-01-15'),
       (8,  '1997-01-13', '1997-01-14'),
       (9,  '1997-01-14', '1997-01-14'),
       (10, '1997-01-17', '1997-01-17');

解惑 #1

SELECT T1.start_date, MAX(T2.end_date)

  FROM Timesheets AS T1, Timesheets AS T2

WHERE T1.start_date <= T2.end_date

   AND NOT EXISTS

       (SELECT *

          FROM Timesheets AS T3, Timesheets AS T4

         WHERE T3.end_date < T4.start_date

           AND T3.start_date >= T1.start_date

           AND T3.end_date <= T2.end_date

           AND T4.start_date >= T1.start_date

           AND T4.end_date <= T2.end_date

           AND NOT EXISTS

               (SELECT *

                  FROM Timesheets AS T5

                 WHERE T5.start_date BETWEEN T3.start_date AND T3.end_date

                   AND T5.end_date BETWEEN T4.start_date AND T4.end_date))

GROUP BY T1.start_date

HAVING T1.start_date = MIN(t2.start_date);

结果

start_date    end date

======================

1997-01-01  1997-01-05

1997-01-06  1997-01-09

1997-01-12  1997-01-15

1997-01-17  1997-01-17

解惑 #2

这个查询很长，但是检查一下查询时间。

SELECT X.start_date, MIN(Y.end_date) AS end_date

  FROM (SELECT T1.start_date

          FROM Timesheets AS T1

               LEFT OUTER JOIN

               Timesheets AS T2

               ON T1.start_date > T2.start_date

                  AND T1.start_date <= T2.end_date

         GROUP BY T1.start_date

        HAVING COUNT(T2.start_date) = 0) AS X(start_date)

       INNER JOIN

       (SELECT T3.end_date

          FROM Timesheets AS T3

               LEFT OUTER JOIN

               Timesheets AS T4

               ON T3.end_date >= T4.start_date

                  AND T3.end_date < T4.end_date

         GROUP BY T3.end_date

        HAVING COUNT(T4.start_date) = 0) AS Y(end_date)

     ON X.start_date <= Y.end_date

  GROUP BY X.start_date;

结果

start_date    end date

=======================

1997-01-01   1997-01-05

1997-01-06   1997-01-09

1997-01-12   1997-01-15

1997-01-17   1997-01-17

解惑 #3

SELECT X.start_date, MIN(X.end_date) AS end_date

  FROM (SELECT T1.start_date, T2.end_date

          FROM Timesheets AS T1, Timesheets AS T2, Timesheets AS T3

         WHERE T1.end_date <= T2.end_date

         GROUP BY T1.start_date, T2.end_date

        HAVING MAX (CASE

                    WHEN (T1.start_date > T3.start_date

                         AND T1.start_date <= T3.end_date)

                         OR (T2.end_date >= T3.start_date

                            AND T2.end_date < T3.end_date)

                    THEN 1 ELSE 0 END) = 0) AS X

GROUP BY X.start_date;

结果

start_date    end_date

========================

1997-01-01   1997-01-05

1997-01-06   1997-01-09

1997-01-12   1997-01-15

1997-01-17   1997-01-17

这个小小的查询中包含了很多逻辑。

CREATE TABLE Timesheets
(task_id CHAR(10) NOT NULL PRIMARY KEY,
start_date DATE NOT NULL,
end_date DATE NOT NULL,
CHECK(start_date <= end_date));

INSERT INTO TIMESHEETS VALUES (1,  DATE '1997-01-01', DATE '1997-01-03');
INSERT INTO TIMESHEETS VALUES (2,  DATE '1997-01-02', DATE '1997-01-04');
INSERT INTO TIMESHEETS VALUES (3,  DATE '1997-01-04', DATE '1997-01-05');
INSERT INTO TIMESHEETS VALUES (4,  DATE '1997-01-06', DATE '1997-01-09');
INSERT INTO TIMESHEETS VALUES (5,  DATE '1997-01-09', DATE '1997-01-09');
INSERT INTO TIMESHEETS VALUES (6,  DATE '1997-01-09', DATE '1997-01-09');
INSERT INTO TIMESHEETS VALUES (7,  DATE '1997-01-12', DATE '1997-01-15');
INSERT INTO TIMESHEETS VALUES (8,  DATE '1997-01-13', DATE '1997-01-14');
INSERT INTO TIMESHEETS VALUES (9,  DATE '1997-01-14', DATE '1997-01-14');
INSERT INTO TIMESHEETS VALUES (10, DATE '1997-01-17', DATE '1997-01-17');

SELECT MIN(start_date)
      ,MAX(end_date)
  FROM (SELECT start_date
              ,end_date
              ,SUM(broken) OVER (ORDER BY start_date,end_date) flag
         FROM (SELECT t.*
                     ,(CASE WHEN start_date <= MAX(end_date) OVER (ORDER BY start_date,end_date ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING)
                           THEN 0
                           ELSE 1
                       END) AS broken
                 FROM Timesheets t
               )
       )  
GROUP BY flag;

newkid

谜题27　找出相等集合

集合理论中，对于子集有两个符号。一个是横向的“马蹄铁”（?），表示集合A包含在集合B中，有时称为真子集。另外一个在这个符号下面加一条横线（?），表示“包含在或等于”，有时称为子集或包含运算符。

标准SQL中从来没有比较几个表的运算符。有些讲述关系型数据库的大学教程提到了标准SQL中不存在的CONTAINS谓词。这样两个违反标准的教程是：Bipin C. Desai 编著的An Introduction to Data Base Systems（West Publishing, 1990, ISBN 0-314-66771-7）以及Elmasri和Navthe编著的Fundamentals of Database Systems（Benjamin Cummings, 1989, ISBN 0-8053-0145-3）。这个谓词以前曾经存在于IBM的第一个实验性的SQL系统System R中，但是因为运行太耗费资源，在后来的SQL实现中把它去掉了。

IN()谓词用来测试从属关系，不能用于子集。如果你们还记得中学时学过的集合理论，就会知道从属关系用希腊字母∈表示，右边是包含集合。从属关系用于元素，而子集本身也是集合，不是元素。

          Chris Date在Database Programming & Design杂志1993年第12期上的谜题（据Date说，是1993年第12期上的Matter of Integrity, Part II）使用了供应商和零件表来查找所有成对的、能够提供完全相同零件的供应商。这与找到两个相等集合是一回事。他的著名的表是：

CREATE TABLE SupParts

(sno CHAR(2) NOT NULL,

pno CHAR(2) NOT NULL,

PRIMARY KEY (sno, pno));

你能够找到多少种方法来解决这个问题？

解惑 #1

一个方法是对每一对供应商都执行FULL OUTER JOIN。对于两个供应商而言不是公共的零件都将会显示出来，但是在从不属于INNER JOIN部分的供应商中导出的一列中将产生NULL。这将告诉你哪几对不匹配，但不能告诉你是谁。最后一个步骤是从所有可能的成对的供应商中删除那些不匹配的。

SELECT SP1.sno, SP2.sno

  FROM SupParts AS SP1

       INNER JOIN

       SupParts AS SP2

       ON SP1.pno = SP2.pno

          AND SP1.sno < SP2.sno

EXCEPT

SELECT DISTINCT SP1.sno, SP2.sno

  FROM SupParts AS SP1

       FULL OUTER JOIN

       SupParts AS SP2

       ON SP1.pno = SP2.pno

          AND SP1.sno < SP2.sno

WHERE SP1.sno IS NULL

OR SP2.sno IS NULL;

这段代码可能运行得很慢。EXCEPT运算符是差集在SQL中的对等物。

解惑 #2

通常用来证明两个集合相等的方法是证明集合A包含集合B，且集合B包含集合A。在标准SQL中通常的做法是证明不存在这样的元素：它在集合A中，但不在集合B中，因此A是B的一个子集。所以首先的尝试通常如下：

SELECT DISTINCT SP1.sno, SP2.sno

  FROM SupParts AS SP1, SupParts AS SP2

WHERE SP1.sno < SP2.sno

   AND SP1.pno IN (SELECT SP22.pno

                     FROM SupParts AS SP22

                    WHERE SP22.sno = SP2.sno)

   AND SP2.pno IN (SELECT SP11.pno

                     FROM SupParts AS SP11

                    WHERE SP11.sno = SP1.sno);

但是，因为如果一对供应商只有一个条目是相同的，他们也将被返回，所以这样不行。

解惑 #3

可以使用NOT EXIST谓词来间接表达在解惑 #2中提到的传统测试。

SELECT DISTINCT SP1.sno, SP2.sno

  FROM SupParts AS SP1, SupParts AS SP2

WHERE SP1.sno < SP2.sno

   AND NOT EXISTS (SELECT SP3.pno -- part in SP1 but not in SP2

                     FROM SupParts AS SP3

                    WHERE SP1.sno = SP3.sno

                      AND SP3.pno

                      NOT IN (SELECT pno

                                FROM SupParts AS SP4

                               WHERE SP2.sno = SP4.sno))

   AND NOT EXISTS (SELECT SP5.pno -- part in SP2 but not in SP1

                     FROM SupParts AS SP5

                    WHERE SP2.sno = SP5.sno

                      AND SP5.pno

                           NOT IN (SELECT pno

                                     FROM SupParts AS SP4

                                    WHERE SP1.sno = SP4.sno));

解惑 #4

我想不使用子集，而是通过另外一种方法来比较集合是否相等。首先，通过他们拥有的共同零件，将一个供应商与另外一个联结起来，消除供应商1就是供应商2的情况，这样就有了两个集合的交集。如果交集中成对零件的数目与两个集合各自元素的数量相等，则这两个集合相等。

SELECT SP1.sno, SP2.sno

  FROM SupParts AS SP1

       INNER JOIN

       SupParts AS SP2

       ON SP1.pno = SP2.pno

          AND SP1.sno < SP2.sno

GROUP BY SP1.sno, SP2.sno

HAVING COUNT(*) = (SELECT COUNT(*)

                     FROM SupParts AS SP3

                    WHERE SP3.sno = SP1.sno)

   AND COUNT(*) = (SELECT COUNT(*)

                     FROM SupParts AS SP4

                    WHERE SP4.sno = SP2.sno);

如果在SupParts表中的供应商号上有索引，它可以直接提供计数，对联结操作也有帮助。

解惑 #5

这个解答与解惑#4相同：

SELECT SP1.sno, SP2.sno

  FROM SupParts AS SP1 INNER JOIN SupParts AS SP2

    ON SP1.pno = SP2.pno

   AND SP1.sno < SP2.sno

WHERE (SELECT COUNT(pno)

          FROM SupParts AS SP3

         WHERE SP3.sno = SP1.sno) = (SELECT COUNT(pno)

                                       FROM SupParts AS SP4

                                      WHERE SP4.sno = SP2.sno)

GROUP BY SP1.sno, SP2.sno

HAVING COUNT(SP1.sno || SP2.sno) = (SELECT COUNT(pno)

                                      FROM SupParts AS SP3

                                     WHERE SP3.sno = SP1.sno);

COUNT (SP1.sno || SP2.sno)测试的技巧是找出与各自表中部件数量相同的一对供应商。

解惑 #6

这是解惑#3的另一个版本，来自Francisco Moreno，用差集代替了NOT EXIST谓词。他使用的是Oracle，其中的EXCEPT（在他们的SQL方言中称为MINUS）运行得相当快。

SELECT DISTINCT SP1.sno, SP2.sno

  FROM SupParts AS SP1, SupParts AS SP2

WHERE SP1.sno < SP2.sno

   AND NOT EXISTS (SELECT SP3.pno -- part in SP1 but not in SP2

                     FROM SupParts AS SP3

                    WHERE SP1.sno = SP3.sno

                   EXCEPT

                   SELECT SP4.pno

                     FROM SupParts AS SP4

                    WHERE SP2.sno = SP4.sno)

   AND NOT EXISTS (SELECT SP5.pno -- part in SP2 but notin

                     FROM SupParts AS SP5

                    WHERE SP2.sno = SP5.sno

                   EXCEPT

                   SELECT SP6.pno

                     FROM SupParts AS SP6

                    WHERE SP1.sno = SP6.sno);

解惑 #7

Alexander Kuznetsov又一次提交了解决方法，改进了“在联结中计算匹配数量”的老方法：

SELECT A.sno, B.sno AS sno1

  FROM (SELECT sno, COUNT(*), MIN(pno), MAX(pno)

          FROM SupParts GROUP BY sno)

       AS A(sno, cnt, min_pno, max_pno)

       INNER JOIN

       (SELECT sno, COUNT(*), MIN(pno), MAX(pno)

          FROM SupParts GROUP BY sno)

       AS B(sno, cnt, min_pno, max_pno)

-- four conditions filter out most permutations

       ON A.cnt = B.cnt

          AND A.min_pno = B.min_pno

          AND A.max_pno = B.max_pno

          AND A.sno < B.sno

-- Expensive inner select below does not have to execute for every pair

WHERE A.cnt

       = (SELECT COUNT(*)

            FROM SupParts AS A1,

                 SupParts AS B1

           WHERE A1.pno = B1.pno

             AND A1.sno = A.sno

             AND B1.sno = B.sno);

sno sno1

========

ab  bb

aq  pq

这个查询的聪明之处是：因为一个列的MIN()和MAX()值是存储在统计表中的，很多优化程序都能迅速找到它们。

解惑 #8

让我们看一下术语以及相等性的通常测试：

（（A?B）=(B?A)）==> (A=B)

（（A∪B）=(B∩A)）==> (A=B)

第一个等式实际上是使用联结做比较的基础。第二个等式是在集合级而不是在子集级完成的，它暗示了解答：

S SELECT DISTINCT 'not equal'

  FROM (SELECT * FROM A

        UNION

        SELECT * FROM B)

        EXCEPT

       (SELECT * FROM A

        INTERSECT

        SELECT * FROM B);

想法是如果表A和表B相等，就返回空集合。如果有重复，那么在集合运算符上使用ALL子句就要小心了。好消息是这些运算符是在行级别而不是列级别上使用的，这个模板可以推广到任意一对与联合兼容的表。你不需要知道列名。

CREATE TABLE SupParts
(sno CHAR(2) NOT NULL,
pno CHAR(2) NOT NULL,
PRIMARY KEY (sno, pno));

INSERT INTO supparts VALUES ('A','1');
INSERT INTO supparts VALUES ('A','2');
INSERT INTO supparts VALUES ('A','3');
INSERT INTO supparts VALUES ('B','1');
INSERT INTO supparts VALUES ('B','2');
INSERT INTO supparts VALUES ('C','1');
INSERT INTO supparts VALUES ('C','2');
INSERT INTO supparts VALUES ('C','3');
INSERT INTO supparts VALUES ('D','1');
INSERT INTO supparts VALUES ('D','3');
INSERT INTO supparts VALUES ('E','1');
INSERT INTO supparts VALUES ('E','2');

WITH S AS (SELECT sno, COUNT(*) cnt FROM supparts GROUP BY sno)
SELECT sno1,sno2
  FROM (SELECT s1.sno sno1
              ,s2.sno sno2
              ,COUNT(*) cnt
          FROM supparts s1, supparts s2
         WHERE s1.sno<s2.sno AND s1.pno=s2.pno
        GROUP BY s1.sno,s2.sno
       ) ss1
      ,s ss2
      ,s ss3
WHERE ss1.sno1=ss2.sno AND ss1.sno2=ss3.sno AND ss1.cnt = ss2.cnt AND ss1.cnt = ss3.cnt;

newkid

谜题12　索赔状态

Leonard C. Medal在CompuServe上发表了下面的问题。病人对医疗机构提出法律索赔（claim），我们把它记录到Claims表中：

Claims

claim_id patient_name

======================

  10        'Smith'

  20        'Jones'

  30        'Brown'

每一项索赔都有一个或多个被告（defendant），通常是医生，记录在'Defendants'表中：

Defendants

claim_id defendant_name

=======================

  10       'Johnson'

  10       'Meyer'

  10       'Dow'

  20       'Baker'

  20       'Meyer'

  30       'Johnson'

每个与索赔相关的被告都有法律事件（legal event）历史，当某项索赔的被告的索赔状态发生变化时，都会记录下来。

LegalEvents

claim_id  defendant_name claim_status  change_date

==================================================

  10        'Johnson'        'AP'      '1994-01-01'

  10        'Johnson'        'OR'      '1994-02-01'

  10        'Johnson'        'SF'      '1994-03-01'

  10        'Johnson'        'CL'      '1994-04-01'

  10        'Meyer'          'AP'      '1994-01-01'

  10        'Meyer'          'OR'      '1994-02-01'

  10        'Meyer'          'SF'      '1994-03-01'

  10        'Dow'            'AP'      '1994-01-01'

  10        'Dow'            'OR'      '1994-02-01'

  20        'Meyer'          'AP'      '1994-01-01'

  20        'Meyer'          'OR'      '1994-02-01'

  20        'Baker'          'AP'      '1994-01-01'

  30        'Johnson'        'AP'      '1994-01-01'

对于每个被告索赔状态的变化按照法律制订的已知顺序进行，如下面的Claim状态表所示：

ClaimStatusCodes

claim_status  claim_status_desc          claim_seq

==================================================

  'AP'        'Awaiting review panel'        1

  'OR'        'Panel opinion rendered'       2

  'SF'        'Suit filed'                   3

  'CL'        'Closed'                       4

被告（与某个索赔相关）的索赔状态是他或她最近的索赔状态，是具有最高索赔顺序号的索赔状态。由于某个法律原因，以日期排序的法律事件并不总是与按照索赔顺序号排序的法律事件相对应。

某个索赔的索赔状态是所有涉及索赔的被告中索赔状态最低的那个被告的状态。这样索赔状态是最大值中的最小值。对于样例数据，答案将是：

claim_id patient_name claim_status

==================================

  10        'Smith'       'OR'

  20        'Jones'       'AP'

  30        'Brown'       'AP'

问题是找出每一项索赔的索赔状态并显示出来。

       

解惑 #1


Medal先生的答案是一条将描述直接转换为代码的SQL查询：

SELECT C1.claim_id, C1.patient_name, S1.claim_status

  FROM Claims AS C1, ClaimStatusCodes AS S1

WHERE S1.claim_seq

    IN (SELECT MIN(S2.claim_seq)

          FROM ClaimStatusCodes AS S2

         WHERE S2.claim_seq

            IN (SELECT MAX(S3.claim_seq)

                  FROM LegalEvents AS E1, ClaimStatusCodes AS S3

                 WHERE E1.claim_status = S3.claim_status

                   AND E1.claim_id = C1.claim_id

                 GROUP BY E1.defendant_name));

解惑 #2

这将给出病人的所有索赔状态码：

SELECT E1.claim_id, C1.patient_name, E1.claim_status

  FROM LegalEvents AS E1, Claims AS C1

WHERE E1.claim_id = C1.claim_id

GROUP BY E1.claim_id, C1.patient_name, E1.claim_status

解惑 #3

这个解答来自Francisco Moreno，它使用了SQL-92的JOIN句法，在设计上避免了子查询。

SELECT C1.claim_id, C1.patient_name,

         CASE MIN(S1.claim_seq)

         WHEN 2 THEN 'AP'

         WHEN 3 THEN 'OR'

         WHEN 4 THEN 'SF'

         ELSE 'CL' END

  FROM

((Claims AS C1

   INNER JOIN

   Defendants AS D1

   ON C1.claim_id = D1.claim_id)

   CROSS JOIN

   ClaimStatusCodes AS S1)

LEFT OUTER JOIN

LegalEvents AS E1

ON C1.claim_id = E1.claim_id

    AND D1.defendant_name = E1.defendant_name

    AND S1.claim_status = E1.claim_status

WHERE E1.claim_id IS NULL

答案#3有点莫名其妙，估计又是页面不完整。

我的答案：

CREATE TABLE Claims (claim_id NUMBER, patient_name VARCHAR2(20));

INSERT INTO claims VALUES (10,'Smith');
INSERT INTO claims VALUES (20,'Jones');
INSERT INTO claims VALUES (30,'Brown');

CREATE TABLE Defendants (claim_id NUMBER, defendant_name VARCHAR2(20));
----- 此表在查询中并未用到。
INSERT INTO defendants VALUES(10,'Johnson' );
INSERT INTO defendants VALUES(10,'Meyer'   );
INSERT INTO defendants VALUES(10,'Dow'     );
INSERT INTO defendants VALUES(20,'Baker'   );
INSERT INTO defendants VALUES(20,'Meyer'   );
INSERT INTO defendants VALUES(30,'Johnson' );

CREATE TABLE LegalEvents (claim_id NUMBER, defendant_name VARCHAR2(20), claim_status VARCHAR2(2),  change_date DATE);

INSERT INTO legalevents VALUES (10,'Johnson' ,'AP' , DATE '1994-01-01');
INSERT INTO legalevents VALUES (10,'Johnson' ,'OR' , DATE '1994-02-01');
INSERT INTO legalevents VALUES (10,'Johnson' ,'SF' , DATE '1994-03-01');
INSERT INTO legalevents VALUES (10,'Johnson' ,'CL' , DATE '1994-04-01');
INSERT INTO legalevents VALUES (10,'Meyer'   ,'AP' , DATE '1994-01-01');
INSERT INTO legalevents VALUES (10,'Meyer'   ,'OR' , DATE '1994-02-01');
INSERT INTO legalevents VALUES (10,'Meyer'   ,'SF' , DATE '1994-03-01');
INSERT INTO legalevents VALUES (10,'Dow'     ,'AP' , DATE '1994-01-01');
INSERT INTO legalevents VALUES (10,'Dow'     ,'OR' , DATE '1994-02-01');
INSERT INTO legalevents VALUES (20,'Meyer'   ,'AP' , DATE '1994-01-01');
INSERT INTO legalevents VALUES (20,'Meyer'   ,'OR' , DATE '1994-02-01');
INSERT INTO legalevents VALUES (20,'Baker'   ,'AP' , DATE '1994-01-01');
INSERT INTO legalevents VALUES (30,'Johnson' ,'AP' , DATE '1994-01-01');


CREATE TABLE ClaimStatusCodes (claim_status VARCHAR2(2),  claim_status_desc VARCHAR2(40), claim_seq NUMBER);

INSERT INTO claimstatuscodes VALUES ('AP','Awaiting review panel' , 1);
INSERT INTO claimstatuscodes VALUES ('OR','Panel opinion rendered', 2);
INSERT INTO claimstatuscodes VALUES ('SF','Suit filed'            , 3);
INSERT INTO claimstatuscodes VALUES ('CL','Closed'                , 4);

SELECT t.claim_id,patient_name,claim_status
  FROM (SELECT claim_id,MIN(claim_seq) AS min_claim_seq
         FROM (SELECT claim_id,defendant_name,MAX(claim_seq) AS claim_seq
                 FROM LegalEvents l, ClaimStatusCodes cd
                WHERE l.claim_status= cd.claim_status
               GROUP BY claim_id,defendant_name
              )
       GROUP BY claim_id
       ) t
      ,Claims c
      ,ClaimStatusCodes cd
WHERE t.claim_id= c.claim_id
      AND t.min_claim_seq = cd.claim_seq;

〇〇

感谢分享

nyfor