前 N 种情况下不重复组合的 SQL 查询
我需要一个可以在(或作为)函数中使用的查询，并从表中检索 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
相关文章