获取 sqlserver 表中所有行的行条目子集的排列

Question

假设我有这样的表格：

idx  | a1 | a2 | a3 | b1 | c1
-----------------------------
idx1, a11, a12, a13, b11, c11
idx2, a21, a22, a23, b21, c21
idx3, a31, a32, a33, b31, c31
idx4, a41, a42, a43, b41, c41

我想向表中添加具有现有行的列子集(a1、a2、a3(排列的行。例如。row1 将贡献额外的行，例如：

idx  | a1 | a2 | a3 | b1 | c1
-----------------------------
idx1, a11, a12, a13, b11, c11
idx1, a11, a13, a12, b11, c11
idx1, a12, a11, a13, b11, c11
idx1, a12, a13, a11, b11, c11
idx1, a13, a11, a12, b11, c11
idx1, a13, a12, a11, b11, c11
and so on for the other original rows...

我将如何在sqlserver中执行此操作？谢谢

Answer 1

这是一个交叉联接方法。

declare @table table (indx varchar(4), a1 varchar(3), a2 varchar(3), a3 varchar(3), b1 varchar(3), c1 varchar(3))
insert into @table
values
('idx1','a11','a12','a13','b11','c11'),
('idx2','a21','a22','a23','b21','c21')
;with cte as(
select indx, a1
from @table
union
select indx, a2
from @table
union 
select indx, a3
from @table)
select distinct
c1.indx
,c1.a1
,c2.a1 as a2
,c3.a1 as a3
,t.b1
,t.c1
from cte c1
cross join cte c2
cross join cte c3
left join @table t on t.indx = c1.indx
where 
c1.indx = c2.indx  
and c3.indx = c1.indx
and c1.a1 <> c2.a1
and c1.a1 <> c3.a1
and c2.a1 <> c3.a1
order by
c1.indx
,c1.a1
,c2.a1
,c3.a1

返回

+------+-----+-----+-----+-----+-----+
| indx | a1  | a2  | a3  | b1  | c1  |
+------+-----+-----+-----+-----+-----+
| idx1 | a11 | a12 | a13 | b11 | c11 |
| idx1 | a11 | a13 | a12 | b11 | c11 |
| idx1 | a12 | a11 | a13 | b11 | c11 |
| idx1 | a12 | a13 | a11 | b11 | c11 |
| idx1 | a13 | a11 | a12 | b11 | c11 |
| idx1 | a13 | a12 | a11 | b11 | c11 |
| idx2 | a21 | a22 | a23 | b21 | c21 |
| idx2 | a21 | a23 | a22 | b21 | c21 |
| idx2 | a22 | a21 | a23 | b21 | c21 |
| idx2 | a22 | a23 | a21 | b21 | c21 |
| idx2 | a23 | a21 | a22 | b21 | c21 |
| idx2 | a23 | a22 | a21 | b21 | c21 |
+------+-----+-----+-----+-----+-----+

Answer 2

排列，5！ 每个 IDX 的组合，前提是源行中的所有值都不同。

declare @t table(
idx  varchar(10)
, a1 varchar(10)
, a2 varchar(10)
, a3 varchar(10)
, b1 varchar(10)
, c1 varchar(10)
);
insert @t 
values
('idx1','a11','a12','a13','b11','c11')
,('idx2','a21','a22','a23','b21','c21')
,('idx3','a31','a32','a33','b31','c31')
,('idx4','a41','a42','a43','b41','c41');
with pvt as (
select idx,m 
from @t
cross apply ( 
values(a1),(a2),(a3),(b1),(c1)
) vals(m)
)
select t1.idx, t1.m, t2.m, t3.m, t4.m, t5.m
from pvt t1
join pvt t2 on t1.idx=t2.idx and t2.m not in (t1.m)
join pvt t3 on t1.idx=t3.idx and t3.m not in (t1.m, t2.m)
join pvt t4 on t1.idx=t4.idx and t4.m not in (t1.m, t2.m, t3.m)
join pvt t5 on t1.idx=t5.idx and t5.m not in (t1.m, t2.m, t3.m, t4.m)
order by t1.idx, t1.m, t2.m, t3.m, t4.m, t5.m;

Answer 3

你试过下面这样的东西吗？关于 a1、a2、a3 列的独特性和内容，您的问题非常模糊。

WITH cte AS (
SELECT Idx, b1, c1, a1 AS a
UNION
SELECT Idx, b1, c1, a2 AS a
UNION
SELECT Idx, b1, c1, a3 AS a
)
SELECT cte1.Idx, cte1.b1, cte1.c1, cte1.a AS a1, cte2.a AS a2, cte3.a AS a3
FROM cte AS cte1
JOIN cte AS cte2 ON cte1.Idx = cte2.Idx AND cte1.b1 = cte2.b1 AND cte1.c1 = cte2.c1
JOIN cte AS cte3 ON cte1.Idx = cte3.Idx AND cte1.b1 = cte3.b1 AND cte1.c1 = cte3.c1