问题描述
我需要一个可以在(或作为)函数中使用的查询,并从表中检索 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