Puzzle63 - SQLパズル演習

問題

T(num,data)
「各data値がnumの何番から何番まで連続しているのかを、その出現順で集約する」
例：

  num | data          low  |  high | data
 -----+------        ------+-------+-------
    1 | a              1   |   2   | 'a'  
    2 | a　　　　→    3   |   6   | 'b'
    3 | b　　　　      8   |   8   | 'a'
    6 | b
    8 | a

参考回答↓：しかしこれは冗長らしい

 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;

yukaさんの回答

fumikoの回答

SQL

 select (case when t3.num IS NULL and t2.num IS NULL then t1.num
              when t3.num IS NOT NULL then t1.num
              else NULL END) as low,
        (case when t3.num IS NULL and t2.num IS NULL then t1.num
              when t3.num IS NOT NULL then t2.num
              else NULL END) as high,
         t1.data
 from (t as t1 LEFT OUTER JOIN t as t2
       ON t1.num < t2.num and t1.data = t2.data) LEFT OUTER JOIN t as t3
       ON t2.num < t3.num and t2.data <> t3.data

考え方

• dataが同じ、numが一方より大きいという条件で自己結合する(t1,t2)
• 上でできたテーブルと、dataが異なり、numが一方より大きいという条件で自己結合(t1,t2,t3)

       t1      |      t2      |     t3
  num1 | data1 | num2 | data2 | num3 | data3
 ------+-------+------+-------+------+-------
     1 | a     |    8 | a     |      |
     1 | a     |    2 | a     |    3 | b
     1 | a     |    2 | a     |    6 | b
     2 | a     |    8 | a     |      |
     3 | b     |    6 | b     |    8 | a
     6 | b     |      |       |      |
     8 | a     |      |       |      |

• これに対し、

(ア）t3の項目がnullでないならそのタプルのt1.num、t2.numをlow、highにする
(イ）t2の項目がnullならそのタプルのt1.numをlow、highにする

まずい点

• 重複してしまう（ア）
• まちがったものまで含めてしまう（イ）
• いらない行まででてしまう

結果

  low | high | data
 -----+------+------
      |      | a
    1 |    2 | a
    1 |    2 | a
      |      | a
    3 |    6 | b
    6 |    6 | b
    8 |    8 | a