前 N 种情况下不重复组合的 SQL 查询 [英] SQL query for combinations without repetition for top N cases

问题描述

我需要一个可以在(或作为)函数中使用的查询，并从表中检索 M 个值的 N 个组合.

I need a query which can be used in (or as) a function and retrieves N combinations of M values from a table.

示例:输入:多行一列值的表格

Example: Input: table with values in one column in multiple rows

N=2
M=4 (Record1 to Record4)

表格

Record1
Record2
Record3
Record4

输出

Record1
Record2
Record3
Record4
Record1,Record2
Record1,Record3
Record1,Record4
Record2,Record3
Record2,Record4
Record3,Record4

案例 2

N=3
M=4 (Record1 to Record4)

表格

Record1
Record2
Record3
Record4

输出

Record1
Record2
Record3
Record4
Record1,Record2
Record1,Record3
Record1,Record4
Record2,Record3
Record2,Record4
Record3,Record4
Record1,Record2,Record3
Record1,Record2,Record4
Record1,Record3,Record4
Record2,Record3,Record4

我使用这个问题作为执行的基本代码

I am using this question as base code for execution

推荐答案

如果每个组合只需要固定数量的 N 个值，那么它可以在普通 SQL 中轻松完成.

If only a fixed amount of N values per combination is needed, then it can easily be done in a normal SQL.

只需使用 N-1 自连接即可.

Simply by using N-1 self-joins.

例如，如果 N = 3，则 2 个自联接:

For example if N = 3 then 2 self-joins :

SELECT 
CONCAT(t1.name, ',', t2.name, ',', t3.name) AS names
FROM yourtable t1
JOIN yourtable t2 ON t2.name > t1.name
JOIN yourtable t3 ON t3.name > t2.name;

由于在连接中使用了 >，因此不会以不同的顺序返回相同组合的重复项.
(因为 A,B,C = A,C,B = B,A,C = B,C,A = C,A,B = C,B,A)

Because of the use of > in the joins, that wouldn't return duplicates of the same combinations in a different order.
(Since A,B,C = A,C,B = B,A,C = B,C,A = C,A,B = C,B,A)

如果 N 是变量，那么可以在动态 Sql 中使用这种方法，将 N-1 个连接添加到查询字符串中.

If N is variable, then such method could be used in a Dynamic Sql that adds N-1 joins to the query string.

但是，要获得问题的预期输出，还要返回 N=1 &N=2 &N=3 那么我们可以将该技巧与递归 CTE 结合使用.

However, to get the expected output of the question, to return also N=1 & N=2 & N=3 then we could use that trick in combination with a Recursive CTE.

例如这个 T-SQL 片段:

declare @yourtable table ([name] varchar(30));
insert into @yourtable ([name]) values 
('Record1'), 
('Record2'), 
('Record3'), 
('Record4');

WITH RCTE AS 
(
    SELECT 1 as N, t.name as prev_name, cast(t.name as varchar(max)) AS names
    FROM @yourtable t

    UNION ALL

    SELECT N + 1, t.name, CONCAT(r.names,','+ t.name)
    FROM @yourtable t
    JOIN RCTE r ON t.name > r.prev_name AND r.N < 3
)
SELECT names
FROM RCTE
ORDER BY N, names;

退货:

names
------------------------
Record1
Record2
Record3
Record4
Record1,Record2
Record1,Record3
Record1,Record4
Record2,Record3
Record2,Record4
Record3,Record4
Record1,Record2,Record3
Record1,Record2,Record4
Record1,Record3,Record4
Record2,Record3,Record4

这篇关于前 N 种情况下不重复组合的 SQL 查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！