|
■No99160 (OA さん) に返信
> {1〜N}の組み合わせを求めるのに、N個のテーブルをクロス結合しました。
> こちらは、簡単にできたのですが、今度は順列を求めようと以下のSQLを考えたのですが、
> {1〜4}、{1〜5}と組み合わせが増えるとWHERE句が冗長な表現になります。
> WHERE句を、もっと簡単な表現で書けないでしょうか？
たとえば Ｐ(n, k) の順列だと、自分なら「再帰 SQL」を使ってこう書きます。
WITH TBL (NUM) AS (
SELECT NUM = 1 UNION ALL
SELECT NUM + 1 FROM TBL WHERE NUM < n
) SELECT
T1.NUM AS C1
, T2.NUM AS C2
, T3.NUM AS C3
：
：
, Tk.NUM AS Ck
FROM TBL T1
JOIN TBL T2 ON T2.NUM <> T1.NUM
JOIN TBL T3 ON T3.NUM <> T2.NUM AND T3.NUM <> T1.NUM
：
：
JOIN TBL Tk ON Tk.NUM <> T[k-1].NUM AND Tk.NUM <> T[k-2].NUM …… AND Tk.NUM <> T1.NUM
具体例として Ｐ(6, 6) の順列の場合はこうなります。720 パターンが列挙されるはず。
ORDER BY するかはお好みで。
WITH TBL (NUM) AS (
SELECT NUM = 1 UNION ALL
SELECT NUM + 1 FROM TBL WHERE NUM < 6
) SELECT
T1.NUM AS C1
, T2.NUM AS C2
, T3.NUM AS C3
, T4.NUM AS C4
, T5.NUM AS C5
, T6.NUM AS C6
FROM TBL T1
JOIN TBL T2 ON T2.NUM <> T1.NUM
JOIN TBL T3 ON T3.NUM <> T2.NUM AND T3.NUM <> T1.NUM
JOIN TBL T4 ON T4.NUM <> T3.NUM AND T4.NUM <> T2.NUM AND T4.NUM <> T1.NUM
JOIN TBL T5 ON T5.NUM <> T4.NUM AND T5.NUM <> T3.NUM AND T5.NUM <> T2.NUM AND T5.NUM <> T1.NUM
JOIN TBL T6 ON T6.NUM <> T5.NUM AND T6.NUM <> T4.NUM AND T6.NUM <> T3.NUM AND T6.NUM <> T2.NUM AND T6.NUM <> T1.NUM
Ｐ(6, 3) の順列だと 120パターンですね。
WITH TBL (NUM) AS (
SELECT NUM = 1 UNION ALL
SELECT NUM + 1 FROM TBL WHERE NUM < 6
) SELECT
T1.NUM AS C1
, T2.NUM AS C2
, T3.NUM AS C3
FROM TBL T1
JOIN TBL T2 ON T2.NUM <> T1.NUM
JOIN TBL T3 ON T3.NUM <> T2.NUM AND T3.NUM <> T1.NUM
|