如何基于1-69之间的数字列表创建5个数字排列的列表? [英] How do I create a list of 5 number permutations based on a list of numbers ranging from 1-69?

问题描述

我有一个SQL Server DB，其中包含NM1-NM69列及其字段值中的对应数字:

I have a SQL Server DB containing columns NM1-NM69 with their corresponding numbers in the field values:

NM1 | NM2 | NM3 | NM4 | NM5 |...... | NM69
------------------------------------------
1   | 2   | 3   | 4   | 5   |...... | 69

我正在尝试找出此列表的所有5个数字排列.我该如何以一种简单，轻松(好..容易)的方式做到这一点?

I am trying to figure out all 5 number permutations of this list. How do I do that in a simple and easy (well..easier) way?

示例输出:

PM NM1 | PM NM2 | PM NM3 | PM NM4 | PM NM5 |
--------------------------------------------
68     | 2      | 55     | 43     | 52     |

我正在使用SQL

推荐答案

您将数据UNPIVOT分成一列，然后交叉联接五次.这是一个SQL Fiddle排列的5列，选择3​​. SQL Fiddle

You UNPIVOT your data into one column and then CROSS JOIN five times. Here is a SQL Fiddle permuting 5 columns picking 3. SQL Fiddle

DECLARE @t TABLE (Field1 INT, Field2 INT, Field3 INT, Field4 INT, Field5 INT)

INSERT INTO @t VALUES (1,2,3,4,5)

; WITH cte AS (
    SELECT u.FieldValue
    FROM @t
    UNPIVOT (
        [FieldValue] FOR [FieldName] IN (
            Field1, Field2, Field3, Field4, Field5
        )
    ) u
)

SELECT
    t1.FieldValue AS PermuteField1,
    t2.FieldValue AS PermuteField2,
    t3.FieldValue AS PermuteField3
FROM
    cte AS t1 CROSS JOIN
    cte AS t2 CROSS JOIN
    cte AS t3
WHERE
    t1.FieldValue <> t2.FieldValue AND
    t1.FieldValue <> t3.FieldValue AND
    t2.FieldValue <> t3.FieldValue

这篇关于如何基于1-69之间的数字列表创建5个数字排列的列表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！