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

〇〇

记得你用MODEL子句做过

newkid

谜题43　毕业

题目大意：有一张表记录了学生姓名和科目学分，另一张表则告诉你某学科要多少学分才能毕业。学生必须修完所有学科的学分才能毕业。要求输出学生名单并指明哪些人可以毕业哪些人不可以。


CREATE TABLE Categories
(credit_cat CHAR(1) NOT NULL,
rqd_credits INTEGER NOT NULL);

CREATE TABLE CreditsEarned -- no primary key
(student_name CHAR(10) NOT NULL,
credit_cat CHAR(1) NOT NULL,
credits INTEGER NOT NULL);

INSERT INTO Categories VALUES ('A', 10);
INSERT INTO Categories VALUES ('B', 3);
INSERT INTO Categories VALUES ('C', 5);

INSERT INTO CreditsEarned VALUES ('Joe', 'A', 3)   ;
INSERT INTO creditsearned VALUES ('Joe', 'A', 2)   ;
INSERT INTO creditsearned VALUES ('Joe', 'A', 3)   ;
INSERT INTO creditsearned VALUES ('Joe', 'A', 3)   ;
INSERT INTO creditsearned VALUES ('Joe', 'B', 3)   ;
INSERT INTO creditsearned VALUES ('Joe', 'C', 3)   ;
INSERT INTO creditsearned VALUES ('Joe', 'C', 2)   ;
INSERT INTO creditsearned VALUES ('Joe', 'C', 3)   ;
INSERT INTO creditsearned VALUES ('Bob', 'A', 2)   ;
INSERT INTO creditsearned VALUES ('Bob', 'C', 2)   ;
INSERT INTO creditsearned VALUES ('Bob', 'A', 12)  ;
INSERT INTO creditsearned VALUES ('Bob', 'C', 4)   ;
INSERT INTO creditsearned VALUES ('John', 'A', 1)  ;
INSERT INTO creditsearned VALUES ('John', 'B', 100);
INSERT INTO creditsearned VALUES ('Mary', 'A', 1)  ;  
INSERT INTO creditsearned VALUES ('Mary', 'A', 1)  ;  
INSERT INTO creditsearned VALUES ('Mary', 'A', 1)  ;
INSERT INTO creditsearned VALUES ('Mary', 'A', 1)  ;  
INSERT INTO creditsearned VALUES ('Mary', 'A', 1)  ;
INSERT INTO creditsearned VALUES ('Mary', 'A', 1)  ;
INSERT INTO creditsearned VALUES ('Mary', 'A', 1)  ;  
INSERT INTO creditsearned VALUES ('Mary', 'A', 1)  ;  
INSERT INTO creditsearned VALUES ('Mary', 'A', 1)  ;
INSERT INTO creditsearned VALUES ('Mary', 'A', 1)  ;  
INSERT INTO creditsearned VALUES ('Mary', 'A', 1)  ;  
INSERT INTO creditsearned VALUES ('Mary', 'B', 1)  ;
INSERT INTO creditsearned VALUES ('Mary', 'B', 1)  ;  
INSERT INTO creditsearned VALUES ('Mary', 'B', 1)  ;  
INSERT INTO creditsearned VALUES ('Mary', 'B', 1)  ;
INSERT INTO creditsearned VALUES ('Mary', 'B', 1)  ;  
INSERT INTO creditsearned VALUES ('Mary', 'B', 1)  ;  
INSERT INTO creditsearned VALUES ('Mary', 'B', 1)  ;
INSERT INTO creditsearned VALUES ('Mary', 'C', 1)  ;  
INSERT INTO creditsearned VALUES ('Mary', 'C', 1)  ;  
INSERT INTO creditsearned VALUES ('Mary', 'C', 1)  ;
INSERT INTO creditsearned VALUES ('Mary', 'C', 1)  ;  
INSERT INTO creditsearned VALUES ('Mary', 'C', 1)  ;  
INSERT INTO creditsearned VALUES ('Mary', 'C', 1)  ;
INSERT INTO creditsearned VALUES ('Mary', 'C', 1)  ;  
INSERT INTO creditsearned VALUES ('Mary', 'C', 1)  ;

期望结果：
STUDENT_NAME         grad nograd
-------------------- ---- --
Mary                 X  
Joe                  X  
John                      X
Bob                       X

书中答案：
SELECT X.student_name,
       CASE WHEN COUNT(C1.credit_cat)>= (SELECT COUNT(*) FROM Categories)
            THEN 'X'
            ELSE ' '
       END AS grad,
       CASE WHEN COUNT(C1.credit_cat)< (SELECT COUNT(*) FROM Categories)
            THEN 'X'
            ELSE ' '
       END AS nograd
FROM (SELECT student_name, credit_cat, SUM(credits) AS cat_credits
       FROM CreditsEarned
     GROUP BY student_name, credit_cat) X
     LEFT OUTER JOIN
     Categories C1
     ON X.credit_cat = C1.credit_cat
        AND X.cat_credits >= C1.rqd_credits
GROUP BY X.student_name;

我的答案，利用ORACLE的COUNT分析函数：

SELECT t.*
      ,CASE WHEN grad='X' THEN ' ' ELSE 'X' END AS nograd
  FROM (SELECT X.student_name,
               CASE WHEN COUNT(CASE WHEN X.cat_credits>=C1.rqd_credits THEN 1 END)>= MAX(cnt)
                    THEN 'X'
                    ELSE ' '
               END AS grad
        FROM (SELECT student_name, credit_cat, SUM(credits) AS cat_credits
               FROM CreditsEarned
             GROUP BY student_name, credit_cat) X
             JOIN (SELECT c1.*, COUNT(*) OVER() CNT FROM Categories C1) C1
             ON X.credit_cat = C1.credit_cat
        GROUP BY X.student_name
        ) t;

newkid

原帖由 〇〇 于 2010-3-15 18:51 发表
记得你用MODEL子句做过

用MODEL做排列组合？想不起来了。

newkid

谜题 49 产品计件

有一张产品表：
CREATE TABLE Production
(production_center INTEGER NOT NULL,  -- 生产中心
wk_date   DATE NOT NULL,             -- 生产日期
batch_nbr INTEGER NOT NULL,          -- 批号
widget_cnt INTEGER NOT NULL,         -- 本批产量
PRIMARY KEY (production_center, wk_date, batch_nbr));

老板要求把每天、每生产中心的记录均分为三组，计算每组的平均产品。

例如在 2 月 24 号, 第42号生产中心总共有21个批次，你的报表要把数据分为三组：1-7批，8-14批, 15-21批
显示每组的平均产量。

INSERT INTO Production VALUES (42,DATE '2010-2-24',1 , 110);
INSERT INTO Production VALUES (42,DATE '2010-2-24',2 , 120);
INSERT INTO Production VALUES (42,DATE '2010-2-24',3 , 130);
INSERT INTO Production VALUES (42,DATE '2010-2-24',4 , 140);
INSERT INTO Production VALUES (42,DATE '2010-2-24',5 , 150);
INSERT INTO Production VALUES (42,DATE '2010-2-24',6 , 160);
INSERT INTO Production VALUES (42,DATE '2010-2-24',7 , 170);
INSERT INTO Production VALUES (42,DATE '2010-2-24',8 , 180);
INSERT INTO Production VALUES (42,DATE '2010-2-24',9 , 190);
INSERT INTO Production VALUES (42,DATE '2010-2-24',10, 100);
INSERT INTO Production VALUES (42,DATE '2010-2-24',11, 110);
INSERT INTO Production VALUES (42,DATE '2010-2-24',12, 120);
INSERT INTO Production VALUES (42,DATE '2010-2-24',13, 130);
INSERT INTO Production VALUES (42,DATE '2010-2-24',14, 140);
INSERT INTO Production VALUES (42,DATE '2010-2-24',15, 150);
INSERT INTO Production VALUES (42,DATE '2010-2-24',16, 160);
INSERT INTO Production VALUES (42,DATE '2010-2-24',17, 170);
INSERT INTO Production VALUES (42,DATE '2010-2-24',18, 180);
INSERT INTO Production VALUES (42,DATE '2010-2-24',19, 190);
INSERT INTO Production VALUES (42,DATE '2010-2-24',20, 100);
INSERT INTO Production VALUES (42,DATE '2010-2-24',21, 110);





书中答案：
先建一个视图：
CREATE VIEW Prod3 (production_center, wk_date, widget_cnt,third)
AS SELECT v1.production_center, v1.wk_date, widget_cnt,
   CASE WHEN batch_nbr <= cont/3 THEN 1
        WHEN batch_nbr > (2 * cont)/3 THEN 3
        ELSE 2
   END
FROM Production
    ,(SELECT production_center,wk_date,COUNT(*) AS cont
        FROM Production
      GROUP BY production_center,wk_date) V1
WHERE V1.production_center = Production.production_center
      AND V1.wk_date = Production.wk_date;

或者用一种更笨的方法：
CREATE VIEW Prod3 (production_center, wk_date, third, batch_nbr, widget_cnt)
AS SELECT production_center, wk_date, 1, batch_nbr,widget_cnt
     FROM Production  P1
    WHERE batch_nbr <= (SELECT MAX(batch_nbr)/3
                          FROM Production  P2
                       WHERE P1.production_center = P2.production_center
                             AND P1.wk_date = P2.wk_date)
   UNION
   SELECT production_center, wk_date, 2, batch_nbr, widget_cnt
     FROM Production  P1
    WHERE batch_nbr > (SELECT MAX(batch_nbr)/3
                         FROM Production  P2
                         WHERE P1.production_center = P2.production_center
                               AND P1.wk_date = P2.wk_date)
          AND batch_nbr <= (SELECT 2 * MAX(batch_nbr)/3
                              FROM Production  P2
                             WHERE P1.production_center = P2.production_center
                                   AND P1.wk_date = P2.wk_date)
   UNION
   SELECT production_center, wk_date, 3, batch_nbr,widget_cnt
     FROM Production  P1
    WHERE batch_nbr > (SELECT 2 * MAX(batch_nbr)/3
                         FROM Production  P2
                        WHERE P1.production_center =P2.production_center
                              AND P1.wk_date = P2.wk_date);

然后在此视图基础上查询：
SELECT production_center, wk_date, third, COUNT(batch_nbr),
       AVG(widget_cnt)
FROM Prod3
GROUP BY production_center, wk_date, third;

幸运的是咱们有一个优雅的分析函数NTILE, 解决这个问题简直不费吹灰之力：

SELECT production_center
      ,wk_date
      ,third
      ,AVG(widget_cnt)
  FROM (SELECT p.*
              ,NTILE(3) OVER(PARTITION BY production_center, wk_date ORDER BY batch_nbr) AS third
          FROM Production p
        )
GROUP BY production_center,wk_date,third;

newkid

谜题53　按列折叠表

表和数据：
CREATE TABLE Foobar
(lvl INTEGER NOT NULL PRIMARY KEY,
color VARCHAR(10),
length INTEGER,
width INTEGER,
hgt INTEGER);

INSERT INTO Foobar VALUES (1, 'RED', 8, 10, 12);
INSERT INTO Foobar VALUES (2, NULL, NULL, NULL, 20);
INSERT INTO Foobar VALUES (3, NULL, 9, 82, 25);
INSERT INTO Foobar VALUES (4, 'BLUE', NULL, 67, NULL);
INSERT INTO Foobar VALUES (5, 'GRAY', NULL, NULL, NULL);

现在需要自底向上把各列折叠起来，即从LVL最大的开始往上找到第一个非空的列。
本例要求返回：
'GRAY', 9, 67, 25


Answer #1
此答案思路是逐一求出每个列自底下上第一次出现非空值的位置，然后把这些行的列值求出来，再并到同一行显示。
John Gilson came up with two answers:
-- Option 1 uses scalar subqueries
SELECT (SELECT color FROM Foobar WHERE lvl = M.lc) AS color,
       (SELECT length FROM Foobar WHERE lvl = M.ll) AS length,
       (SELECT width FROM Foobar WHERE lvl = M.lw) AS width,
       (SELECT hgt FROM Foobar WHERE lvl = M.lh) AS hgt
  FROM (SELECT MAX(CASE WHEN color IS NOT NULL THEN lvl END) AS lc,
               MAX(CASE WHEN length IS NOT NULL THEN lvl END) AS ll,
               MAX(CASE WHEN width IS NOT NULL THEN lvl END) AS lw,
               MAX(CASE WHEN hgt IS NOT NULL THEN lvl END) AS lh
          FROM Foobar) M;

-- Option 2
SELECT MIN(CASE WHEN Foobar.lvl = M.lc THEN Foobar.color END) AS color,
       MIN(CASE WHEN Foobar.lvl = M.ll THEN Foobar.length END) AS length,
       MIN(CASE WHEN Foobar.lvl = M.lw THEN Foobar.width END) AS width,
       MIN(CASE WHEN Foobar.lvl = M.lh THEN Foobar.hgt END) AS hgt
  FROM (SELECT MAX(CASE WHEN color IS NOT NULL THEN lvl END) AS lc,
               MAX(CASE WHEN length IS NOT NULL THEN lvl END) AS ll,
               MAX(CASE WHEN width IS NOT NULL THEN lvl END) AS lw,
               MAX(CASE WHEN hgt IS NOT NULL THEN lvl END) AS lh
          FROM Foobar) M
       INNER JOIN
       Foobar
       ON Foobar.lvl IN (M.lc, M.ll, M.lw, M.lh);


Answer #2
这个答案利用了多次自连接，在我看来实在是不怎么样。
This was my attempt. COALESCE is executed in the order written, so I can
go from bottom to top easily to get the first non-NULL back.

SELECT COALESCE (F5.color, F4.color, F3.color, F2.color,F1.color) AS color,
       COALESCE (F5.length, F4.length, F3.length, F2.length,F1.length) AS length,
       COALESCE (F5.width, F4.width, F3.width, F2.width,F1.width) AS width,
       COALESCE (F5.hgt, F4.hgt, F3.hgt, F2.hgt, F1.hgt) AS hgt
  FROM Foobar F1, Foobar F2, Foobar F3,Foobar F4, Foobar F5
WHERE F1.lvl = 1
      AND F2.lvl = 2
      AND F3.lvl = 3
      AND F4.lvl = 4
      AND F5.lvl = 5;

我的答案：

SELECT SUBSTR(MAX(CASE WHEN color  IS NOT NULL THEN LPAD(lvl,10)||color  END),11) AS color
      ,SUBSTR(MAX(CASE WHEN length IS NOT NULL THEN LPAD(lvl,10)||length END),11) AS length
      ,SUBSTR(MAX(CASE WHEN width  IS NOT NULL THEN LPAD(lvl,10)||width  END),11) AS width
      ,SUBSTR(MAX(CASE WHEN hgt    IS NOT NULL THEN LPAD(lvl,10)||hgt    END),11) AS hgt   
  FROM Foobar;


利用10G的IGNORE NULLS的分析函数：
SELECT * FROM (
SELECT LAST_VALUE(color  IGNORE NULLS) OVER (ORDER BY lvl) AS color
      ,LAST_VALUE(length IGNORE NULLS) OVER (ORDER BY lvl) AS length
      ,LAST_VALUE(width  IGNORE NULLS) OVER (ORDER BY lvl) AS width
      ,LAST_VALUE(hgt    IGNORE NULLS) OVER (ORDER BY lvl) AS hgt   
      ,ROW_NUMBER() OVER(ORDER BY lvl DESC) rn
      ,LVL
  FROM Foobar
)
WHERE rn=1;

[ 本帖最后由 newkid 于 2010-3-16 03:56 编辑 ]

newkid

谜题 62 REPORT FORMATTING 报表格式化

题目大意：有这样一张姓名表
CREATE TABLE Names
(name VARCHAR(15) NOT NULL PRIMARY KEY);

INSERT INTO Names VALUES ('Al')      ;
INSERT INTO NAMES VALUES ('Ben')     ;
INSERT INTO NAMES VALUES ('Charlie') ;
INSERT INTO NAMES VALUES ('David')   ;
INSERT INTO NAMES VALUES ('Ed')      ;
INSERT INTO NAMES VALUES ('Frank')   ;
INSERT INTO NAMES VALUES ('Greg')    ;
INSERT INTO NAMES VALUES ('Howard')  ;
INSERT INTO NAMES VALUES ('Ida')     ;
INSERT INTO NAMES VALUES ('Joe')     ;
INSERT INTO NAMES VALUES ('Ken')     ;
INSERT INTO NAMES VALUES ('Larry')   ;
INSERT INTO NAMES VALUES ('Mike')    ;

需要得到这样格式的报表：按姓名排序后以三列逐行输出
name1 name2  name3
========================
Al    Ben    Charlie
David Ed     Frank
Greg  Howard Ida
Joe   Ken    Larry
Mike  NULL   NULL

如果按四列输出则为：
name1 name2 name3   name4      
==============================
Al    Ben   Charlie David         
Ed    Frank Greg    Howard         
Ida   Joe   Ken     Larry            
Mike  NULL  NULL    NULL           

以下来自书中的答案都已被我改写为ORACLE的语法，所有排序都是我加上的，#5有个错误已被改正。

Answer #1:
SELECT N1.name,               
       MIN(N2.name) AS name2,         
       MIN(N3.name) AS name3,         
       MIN(N4.name) AS name4,         
       MIN(N5.name) AS name5         
  FROM (Names  N1              
        LEFT OUTER JOIN               
        Names  N2                    
        ON N1.name < N2.name)         
       LEFT OUTER JOIN               
       Names N3                    
       ON N1.name < N2.name           
          AND N2.name < N3.name         
       LEFT OUTER JOIN               
       Names N4                    
       ON N1.name < N2.name           
          AND N2.name < N3.name         
          AND N3.name < N4.name         
       LEFT OUTER JOIN               
       Names N5                    
       ON N1.name < N2.name           
          AND N2.name < N3.name         
          AND N3.name < N4.name         
          AND N4.name < N5.name         
  WHERE N1.name IN (SELECT A.name
                      FROM Names A
                           INNER JOIN                     
                           Names B                     
                           ON A.name <= B.name            
                    GROUP BY A.name               
                    HAVING MOD(COUNT(B.name), 5) = (SELECT MOD(COUNT(*), 5) FROM Names)
                    )
GROUP BY N1.name
ORDER BY 1        
;  


Answer #2
Another shorter version of the above query is as follows:
SELECT N3.name, MIN(N4.name), MIN(N5.name), MIN(N6.name),
      MIN(N7.name)
FROM (SELECT N1.name
        FROM Names N1
             INNER JOIN
             Names N2
             ON N1.name >= N2.name
      GROUP BY N1.name
     HAVING MOD (COUNT(*), 5) = 1) N3
     LEFT OUTER JOIN
     Names N4
     ON N3.name < N4.name
     LEFT OUTER JOIN
     Names N5
     ON N4.name < N5.name
     LEFT OUTER JOIN
     Names N6
     ON N5.name < N6.name
     LEFT OUTER JOIN
     Names N7
     ON N6.name < N7.name
GROUP BY N3.name
ORDER BY 1
;

Answer #3
改自Answer #1, 去掉了重复的JOIN条件：

SELECT N1.name,               
       MIN(N2.name) AS name2,         
       MIN(N3.name) AS name3,         
       MIN(N4.name) AS name4,         
       MIN(N5.name) AS name5         
  FROM (Names  N1              
        LEFT OUTER JOIN               
        Names  N2                    
        ON N1.name < N2.name)         
       LEFT OUTER JOIN               
       Names N3                    
       ON N2.name < N3.name         
       LEFT OUTER JOIN               
       Names N4                    
       ON N3.name < N4.name         
       LEFT OUTER JOIN               
       Names N5                    
       ON N4.name < N5.name         
  WHERE N1.name IN (SELECT A.name
                      FROM Names A
                           INNER JOIN                     
                           Names B                     
                           ON A.name <= B.name            
                    GROUP BY A.name               
                    HAVING MOD(COUNT(B.name), 5) = (SELECT MOD(COUNT(*), 5) FROM Names)
                    )
GROUP BY N1.name
ORDER BY 1   
;  


Answer #4
IN里面的子查询可以改写为：
SELECT A.name
  FROM Names A
       INNER JOIN                     
       Names B                     
       ON A.name >= B.name            
GROUP BY A.name               
HAVING MOD(COUNT(*),5) = 1;


Answer #5

SELECT MAX(name1), MAX(name2), MAX(name3), MAX(name4),MAX(name5)
FROM ( -- start of monster table query
      SELECT FLOOR((COUNT(*) - 1) / 5) as cnt,  ----- 原来错误写法：(SELECT (COUNT(*) - 1) / 5),
             (SELECT MAX(N1.name)
                FROM Names  N3
               WHERE N1.name <= N3.name
             HAVING MOD(COUNT(*), 5)= (SELECT MOD(COUNT(*), 5) FROM Names)) name1,
             (SELECT MAX(N1.name)
               FROM Names  N3
             WHERE N1.name <= N3.name
             HAVING MOD (COUNT(*), 5) = (SELECT MOD((COUNT(*) - 1), 5) FROM Names)) name2,
             (SELECT MAX(N1.name)
                FROM Names  N3
              WHERE N1.name <= N3.name
              HAVING MOD(COUNT(*), 5)= (SELECT MOD((COUNT(*) - 2), 5) FROM Names)) name3,
             (SELECT MAX(N1.name)
               FROM Names  N3
               WHERE N1.name <= N3.name
             HAVING MOD(COUNT(*), 5)= (SELECT MOD((COUNT(*) - 3), 5) FROM Names)) name4,
             (SELECT MAX(N1.name)
               FROM Names  N3
             WHERE N1.name <= N3.name
             HAVING MOD(COUNT(*), 5) = (SELECT MOD((COUNT(*) - 4), 5) FROM Names)) name5
      FROM Names  N1
           INNER JOIN
           Names  N2
           ON N1.name >= N2.name
     GROUP BY N1.name
     ) X0
GROUP BY cnt
ORDER BY 1;


Answer #6
I e-mailed these answers to Richard Romley on March 12, 2000, and he
immediately had a cook on the puzzle that he had not shared:

--For 3 columns...
SELECT FirstCol.name AS name1,
       MAX(CASE WHEN OtherCols.cnt = 2
                THEN OtherCols.final_name
                ELSE NULL
           END) AS name2,
       MAX(CASE WHEN OtherCols.cnt = 3
                THEN OtherCols.final_name
                ELSE NULL
           END) AS name3
FROM (SELECT N1.name
        FROM Names  N1, Names  N2
       WHERE N1.name >= N2.name
      GROUP BY N1.name
     HAVING MOD(COUNT(*), 3) = 1) FirstCol
     LEFT OUTER JOIN
     (SELECT N3.name, N5.name final_name, COUNT(*) cnt
        FROM Names  N3, Names  N4, Names  N5
       WHERE N3.name < N5.name
             AND N4.name BETWEEN N3.name AND N5.name
      GROUP BY N3.name, N5.name) OtherCols
ON FirstCol.name = OtherCols.name
GROUP BY FirstCol.name
ORDER BY FirstCol.name;

For 5 columns...
SELECT FirstCol.name AS name1,
       MAX(CASE WHEN OtherCols.cnt = 2
                THEN OtherCols.final_name
                ELSE NULL
           END) AS name2,
       MAX(CASE WHEN OtherCols.cnt = 3
                THEN OtherCols.final_name
                ELSE NULL
           END) AS name3,
       MAX(CASE WHEN OtherCols.cnt = 4
                THEN OtherCols.final_name
                ELSE NULL
           END) AS name4,
       MAX(CASE WHEN OtherCols.cnt = 5
                THEN OtherCols.final_name
                ELSE NULL END) AS name5
FROM (SELECT N1.name
        FROM Names  N1, Names  N2
       WHERE N1.name >= N2.name
      GROUP BY N1.name
      HAVING MOD(COUNT(*), 5) = 1) FirstCol
     LEFT OUTER JOIN
     (SELECT N3.name, N5.name final_name, COUNT(*) cnt
        FROM Names N3, Names N4, Names N5
       WHERE N3.name < N5.name
             AND N4.name BETWEEN N3.name AND N5.name
       GROUP BY N3.name, N5.name) OtherCols
     ON FirstCol.name = OtherCols.name
GROUP BY FirstCol.name
ORDER BY FirstCol.name;

For 6 columns...
SELECT FirstCol.name AS name1,
       MAX(CASE WHEN OtherCols.cnt = 2
                THEN OtherCols.final_name
                ELSE NULL
           END) AS name2,
       MAX(CASE WHEN OtherCols.cnt = 3
                THEN OtherCols.final_name
                ELSE NULL
           END) AS name3,
       MAX(CASE WHEN OtherCols.cnt = 4
                THEN OtherCols.final_name
                ELSE NULL
           END) AS name4,
       MAX(CASE WHEN OtherCols.cnt = 5
                THEN OtherCols.final_name
                ELSE NULL END) AS name5,
       MAX(CASE WHEN OtherCols.cnt = 6
                THEN OtherCols.final_name
                ELSE NULL END) AS name6
FROM (SELECT N1.name
        FROM Names  N1, Names  N2
       WHERE N1.name >= N2.name
      GROUP BY N1.name
      HAVING MOD(COUNT(*), 6) = 1) FirstCol
     LEFT OUTER JOIN
     (SELECT N3.name, N5.name final_name, COUNT(*) cnt
        FROM Names N3, Names N4, Names N5
       WHERE N3.name < N5.name
             AND N4.name BETWEEN N3.name AND N5.name
       GROUP BY N3.name, N5.name) OtherCols
     ON FirstCol.name = OtherCols.name
GROUP BY FirstCol.name
ORDER BY FirstCol.name;

以上答案都写得很辛苦，看得也很辛苦。
隆重推出我的答案, 其实就是个简单的行列转换：

SELECT MAX(CASE WHEN MOD(rn,5)=1 THEN name END) name1
      ,MAX(CASE WHEN MOD(rn,5)=2 THEN name END) name2
      ,MAX(CASE WHEN MOD(rn,5)=3 THEN name END) name3
      ,MAX(CASE WHEN MOD(rn,5)=4 THEN name END) name4
      ,MAX(CASE WHEN MOD(rn,5)=0 THEN name END) name5
  FROM (SELECT name,ROWNUM rn
          FROM (SELECT name FROM names ORDER BY name)
       )
GROUP BY CEIL(rn/5)
ORDER BY CEIL(rn/5);

newkid

PUZZLE 63 CONTIGUOUS GROUPINGS 连续数据分组

Donald Halloran proposed this simple-looking problem:
CREATE TABLE T
(num INTEGER NOT NULL PRIMARY KEY,
data CHAR(1) NOT NULL);

INSERT INTO T VALUES (1, 'a');
INSERT INTO T VALUES (2, 'a');
INSERT INTO T VALUES (3, 'b');
INSERT INTO T VALUES (6, 'b');
INSERT INTO T VALUES (8, 'a');

要求：按num排序，如果相邻的data相同则分为一组。求出每组的最小、最大num和本组的data.

例子输出：
low high data
====================
1   2    a
3   6    b
8   8    a

Answer #1
Here is the first attempt:
SELECT MIN(T1.num) AS low,
       MAX(T1.num) AS high,
       T1.data
  FROM T T1
       LEFT OUTER JOIN
       T T2
       ON T2.num = (SELECT MIN(num)
                      FROM T
                     WHERE num > T1.num
                           AND data <> T1.data)
GROUP BY T1.data, T2.num;
这在ORACLE中行不通。修改：

SELECT MIN(T1.num) AS low,
       MAX(T1.num) AS high,
       T1.data
  FROM (SELECT T1.*,
               (SELECT MIN(num)
                 FROM T
                 WHERE num > T1.num
                       AND data <> T1.data) AS min_num
          FROM t T1
       ) T1
       LEFT OUTER JOIN
       T T2
       ON T2.num = min_num
GROUP BY T1.data, T2.num;


Answer #2
SELECT X.data, MIN(X.low) AS low, X.high
FROM (SELECT T1.data, T1.num low, MAX(T2.num) high
        FROM T T1, T T2
       WHERE T1.num <= T2.num
             AND T1.data = ALL(SELECT T3.data
                                 FROM T T3
                                WHERE T3.num BETWEEN T1.num AND T2.num)
GROUP BY T1.data, T1.num) X
GROUP BY X.data, X.high;

Answer #3
Steve Kass proposed this answer, but did not know whether it's faster,
but this is another approach (a clustered index on (num, data) helps).

SELECT MIN(num) AS low, MAX(num) AS high, data
FROM (SELECT A.num,
              SUM(CASE WHEN A.data = B.data THEN 1 ELSE 0 END) - COUNT(B.num) AS ct,
              A.data
         FROM T A, T B
        WHERE A.num >= B.num
       GROUP BY A.num, A.data
     ) A
GROUP BY data, ct;

He is using a little math to determine that a range has only one data
value in it.

我用ROWNUM技巧写的解法：
SELECT MIN(num),MAX(num),data
  FROM (SELECT t2.*, ROWNUM rn,ROW_NUMBER() OVER(PARTITION BY data ORDER BY num) rn2
          FROM (SELECT * FROM T ORDER BY num) t2
        )
GROUP BY data, rn-rn2
ORDER BY 1;

或者：
SELECT MIN(num),MAX(num),data
  FROM (SELECT t.*
              ,ROW_NUMBER() OVER(ORDER BY num) rn
              ,ROW_NUMBER() OVER(PARTITION BY data ORDER BY num) rn2
          FROM t
        )
GROUP BY data, rn-rn2
ORDER BY 1;

dingjun123

希望newkid好好维护这个帖子，争取把所有谜题用oracle最佳实现，让我等也好好学习一把！然后整理成PDF，哈哈

jackiewss

我认为第一题“谜题18　广告信件”的另一个答案是不是可以这样写：
DELETE FROM consumers WHERE fam IS NULL
AND EXISTS (SELECT * FROM consumers C1
            WHERE consumers.con_id = C1.fam);

luohuap

很好很强大