sql - 列出系列中两个表之间的不同变体
问题描述
我有两个名为 Transaction1 和 Transaction2 的表，它们有不同的步骤：
Create Table Transaction1
(
Step varchar(100)
)
Create Table Transaction2
(
Step varchar(100)
)
Insert into Transaction1 (Step) values
('Read the value from row 4.'),
('Multiply that value times 2.'),
('Write the result to row 3.'),
('Write the literal value “5” to row 2.'),
('Write the literal value “100” to row 5.'),
('Commit.')
Insert into Transaction2 (Step) values
('Read the value from row 2.'),
('Write that value to row 4.'),
('Write the literal value “10” to row 3.'),
('Commit.')
但是，我想在行中列出不同的变化，这些变化必须按顺序发生。例如，我发现的一种可能变化是：
同样，我确信会有许多不同的变体，我不确定如何使用 SQL 来实现。我虽然使用这样的笛卡尔积：
Select t1.*, t2.* from Transaction1 t1, Transaction2 t2
但这不是按顺序保持步骤。如何通过 SQL 实现两个事务的顺序输出？
提前致谢。
解决方案
看来您正在寻找 2^4=16 组合。该代码为输入的每一行分配序数。然后 CROSS JOIN 创建列的每个组合并使用 ROW_NUMBER() 分配一个唯一的“组合”编号。最后，使用 CROSS APPLY 将结果 UNPIVOTED 分成 5 列的 16 个组合。序列中的最后 2 个元素（9 和 10）来自 Transaction1 表。像这样的东西
drop table if exists #Transaction1
go
Create Table #Transaction1(
ordinal int,
Step varchar(100));
drop table if exists #Transaction2
go
Create Table #Transaction2(
ordinal int,
Step varchar(100));
Insert into #Transaction1 (ordinal, Step) values
(1,'Read the value from row 4.'),
(2,'Multiply that value times 2.'),
(3,'Write the result to row 3.'),
(4,'Write the literal value “5” to row 2.'),
(5,'Write the literal value “100” to row 5.'),
(6,'Commit.');
Insert into #Transaction2 (ordinal, Step) values
(1,'Read the value from row 2.'),
(2,'Write that value to row 4.'),
(3,'Write the literal value “10” to row 3.'),
(4,'Commit.');
with
c1_cte(trans, ordinal, step) as (
select 1, ordinal, step from #Transaction1 where ordinal=1
union all
select 2, ordinal, step from #Transaction2 where ordinal=1),
c2_cte(trans, ordinal, step) as (
select 1, ordinal, step from #Transaction1 where ordinal=2
union all
select 2, ordinal, step from #Transaction2 where ordinal=2),
c3_cte(trans, ordinal, step) as (
select 1, ordinal, step from #Transaction1 where ordinal=3
union all
select 2, ordinal, step from #Transaction2 where ordinal=3),
c4_cte(trans, ordinal, step) as (
select 1, ordinal, step from #Transaction1 where ordinal=4
union all
select 2, ordinal, step from #Transaction2 where ordinal=4),
cols_cte as (
select c1.trans t1, c1.ordinal o1, c1.step s1,
c2.trans t3, c2.ordinal o3, c2.step s3,
c3.trans t5, c3.ordinal o5, c3.step s5,
c4.trans t7, c4.ordinal o7, c4.step s7
from c1_cte c1
cross join c2_cte c2
cross join c3_cte c3
cross join c4_cte c4),
both_cte as (
select c.t1, c.o1, c.s1,
seq2.t2, seq2.o2, seq2.s2,
c.t3, c.o3, c.s3,
seq4.t4, seq4.o4, seq4.s4,
c.t5, c.o5, c.s5,
seq6.t6, seq6.o6, seq6.s6,
c.t7, c.o7, c.s7,
seq8.t8, seq8.o8, seq8.s8,
row_number() over (order by (select null)) rn
from cols_cte c
cross apply (select trans t2, ordinal o2, step s2
from c1_cte cc where cc.trans<>c.t1) seq2
cross apply (select trans t4, ordinal o4, step s4
from c2_cte cc where cc.trans<>c.t3) seq4
cross apply (select trans t6, ordinal o6, step s6
from c3_cte cc where cc.trans<>c.t5) seq6
cross apply (select trans t8, ordinal o8, step s8
from c4_cte cc where cc.trans<>c.t7) seq8),
unq_combo_cte as (select distinct rn from both_cte),
t1_last_cte(combo, seq, trans, ordinal, step) as (
select uc.rn, t1.ordinal+4, 1, t1.ordinal, t1.step
from unq_combo_cte uc
cross join #Transaction1 t1
where t1.ordinal in(5, 6))
select v.*
from both_cte b
cross apply (values (b.rn, 1, b.t1, b.o1, b.s1),
(b.rn, 2, b.t2, b.o2, b.s2),
(b.rn, 3, b.t3, b.o3, b.s3),
(b.rn, 4, b.t4, b.o4, b.s4),
(b.rn, 5, b.t5, b.o5, b.s5),
(b.rn, 6, b.t6, b.o6, b.s6),
(b.rn, 7, b.t7, b.o7, b.s7),
(b.rn, 8, b.t8, b.o8, b.s8))
v(combo, seq, trans, ordinal, step)
union all select * from t1_last_cte
order by combo, seq;
combo seq trans ordinal step
1 1 1 1 Read the value from row 4.
1 2 2 1 Read the value from row 2.
1 3 2 2 Write that value to row 4.
1 4 1 2 Multiply that value times 2.
1 5 2 3 Write the literal value “10” to row 3.
1 6 1 3 Write the result to row 3.
1 7 2 4 Commit.
1 8 1 4 Write the literal value “5” to row 2.
1 9 1 5 Write the literal value “100” to row 5.
1 10 1 6 Commit.
2 1 1 1 Read the value from row 4.
2 2 2 1 Read the value from row 2.
2 3 2 2 Write that value to row 4.
2 4 1 2 Multiply that value times 2.
2 5 1 3 Write the result to row 3.
2 6 2 3 Write the literal value “10” to row 3.
2 7 2 4 Commit.
2 8 1 4 Write the literal value “5” to row 2.
2 9 1 5 Write the literal value “100” to row 5.
2 10 1 6 Commit.
3 1 2 1 Read the value from row 2.
3 2 1 1 Read the value from row 4.
3 3 2 2 Write that value to row 4.
3 4 1 2 Multiply that value times 2.
3 5 2 3 Write the literal value “10” to row 3.
3 6 1 3 Write the result to row 3.
3 7 2 4 Commit.
3 8 1 4 Write the literal value “5” to row 2.
3 9 1 5 Write the literal value “100” to row 5.
3 10 1 6 Commit.
4 1 2 1 Read the value from row 2.
4 2 1 1 Read the value from row 4.
4 3 2 2 Write that value to row 4.
4 4 1 2 Multiply that value times 2.
4 5 1 3 Write the result to row 3.
4 6 2 3 Write the literal value “10” to row 3.
4 7 2 4 Commit.
4 8 1 4 Write the literal value “5” to row 2.
4 9 1 5 Write the literal value “100” to row 5.
4 10 1 6 Commit.
5 1 1 1 Read the value from row 4.
5 2 2 1 Read the value from row 2.
5 3 2 2 Write that value to row 4.
5 4 1 2 Multiply that value times 2.
5 5 2 3 Write the literal value “10” to row 3.
5 6 1 3 Write the result to row 3.
5 7 1 4 Write the literal value “5” to row 2.
5 8 2 4 Commit.
5 9 1 5 Write the literal value “100” to row 5.
5 10 1 6 Commit.
6 1 1 1 Read the value from row 4.
6 2 2 1 Read the value from row 2.
6 3 2 2 Write that value to row 4.
6 4 1 2 Multiply that value times 2.
6 5 1 3 Write the result to row 3.
6 6 2 3 Write the literal value “10” to row 3.
6 7 1 4 Write the literal value “5” to row 2.
6 8 2 4 Commit.
6 9 1 5 Write the literal value “100” to row 5.
6 10 1 6 Commit.
7 1 2 1 Read the value from row 2.
7 2 1 1 Read the value from row 4.
7 3 2 2 Write that value to row 4.
7 4 1 2 Multiply that value times 2.
7 5 2 3 Write the literal value “10” to row 3.
7 6 1 3 Write the result to row 3.
7 7 1 4 Write the literal value “5” to row 2.
7 8 2 4 Commit.
7 9 1 5 Write the literal value “100” to row 5.
7 10 1 6 Commit.
8 1 2 1 Read the value from row 2.
8 2 1 1 Read the value from row 4.
8 3 2 2 Write that value to row 4.
8 4 1 2 Multiply that value times 2.
8 5 1 3 Write the result to row 3.
8 6 2 3 Write the literal value “10” to row 3.
8 7 1 4 Write the literal value “5” to row 2.
8 8 2 4 Commit.
8 9 1 5 Write the literal value “100” to row 5.
8 10 1 6 Commit.
9 1 1 1 Read the value from row 4.
9 2 2 1 Read the value from row 2.
9 3 1 2 Multiply that value times 2.
9 4 2 2 Write that value to row 4.
9 5 2 3 Write the literal value “10” to row 3.
9 6 1 3 Write the result to row 3.
9 7 2 4 Commit.
9 8 1 4 Write the literal value “5” to row 2.
9 9 1 5 Write the literal value “100” to row 5.
9 10 1 6 Commit.
10 1 1 1 Read the value from row 4.
10 2 2 1 Read the value from row 2.
10 3 1 2 Multiply that value times 2.
10 4 2 2 Write that value to row 4.
10 5 1 3 Write the result to row 3.
10 6 2 3 Write the literal value “10” to row 3.
10 7 2 4 Commit.
10 8 1 4 Write the literal value “5” to row 2.
10 9 1 5 Write the literal value “100” to row 5.
10 10 1 6 Commit.
11 1 2 1 Read the value from row 2.
11 2 1 1 Read the value from row 4.
11 3 1 2 Multiply that value times 2.
11 4 2 2 Write that value to row 4.
11 5 2 3 Write the literal value “10” to row 3.
11 6 1 3 Write the result to row 3.
11 7 2 4 Commit.
11 8 1 4 Write the literal value “5” to row 2.
11 9 1 5 Write the literal value “100” to row 5.
11 10 1 6 Commit.
12 1 2 1 Read the value from row 2.
12 2 1 1 Read the value from row 4.
12 3 1 2 Multiply that value times 2.
12 4 2 2 Write that value to row 4.
12 5 1 3 Write the result to row 3.
12 6 2 3 Write the literal value “10” to row 3.
12 7 2 4 Commit.
12 8 1 4 Write the literal value “5” to row 2.
12 9 1 5 Write the literal value “100” to row 5.
12 10 1 6 Commit.
13 1 1 1 Read the value from row 4.
13 2 2 1 Read the value from row 2.
13 3 1 2 Multiply that value times 2.
13 4 2 2 Write that value to row 4.
13 5 2 3 Write the literal value “10” to row 3.
13 6 1 3 Write the result to row 3.
13 7 1 4 Write the literal value “5” to row 2.
13 8 2 4 Commit.
13 9 1 5 Write the literal value “100” to row 5.
13 10 1 6 Commit.
14 1 1 1 Read the value from row 4.
14 2 2 1 Read the value from row 2.
14 3 1 2 Multiply that value times 2.
14 4 2 2 Write that value to row 4.
14 5 1 3 Write the result to row 3.
14 6 2 3 Write the literal value “10” to row 3.
14 7 1 4 Write the literal value “5” to row 2.
14 8 2 4 Commit.
14 9 1 5 Write the literal value “100” to row 5.
14 10 1 6 Commit.
15 1 2 1 Read the value from row 2.
15 2 1 1 Read the value from row 4.
15 3 1 2 Multiply that value times 2.
15 4 2 2 Write that value to row 4.
15 5 2 3 Write the literal value “10” to row 3.
15 6 1 3 Write the result to row 3.
15 7 1 4 Write the literal value “5” to row 2.
15 8 2 4 Commit.
15 9 1 5 Write the literal value “100” to row 5.
15 10 1 6 Commit.
16 1 2 1 Read the value from row 2.
16 2 1 1 Read the value from row 4.
16 3 1 2 Multiply that value times 2.
16 4 2 2 Write that value to row 4.
16 5 1 3 Write the result to row 3.
16 6 2 3 Write the literal value “10” to row 3.
16 7 1 4 Write the literal value “5” to row 2.
16 8 2 4 Commit.
16 9 1 5 Write the literal value “100” to row 5.
16 10 1 6 Commit.
推荐阅读
- bash - Git bash - 根据回购不同的颜色
- powershell - 脚本中的事件日志
- java - textToBePresentInElement() 不能使用精确的文本
- java - 在 PreferenceFragment 中使用 getFragmentSupport Manager
- wordpress - 显示或隐藏横幅
- python - 如何将我的日期列转换为日期时间？
- git - 如何长时间设置 git cache --timeout？
- angular - 如何将所有设置的cookie检索到浏览器选项卡角度
- visual-c++ - 将任务划分为子任务并分配给线程池
- r - 在 r 中聚合多个函数