如何使用t-sql在两个完全不相关的表中获取每个排列 [英] How do I get every permutation in two totally unrelated tables with t-sql
问题描述
我有两个与另一个表间接相关的表
I have two tables that are indirectly related by another table
TableA-ID,SomeFieldA
TableA - ID, SomeFieldA
TableB-ID,SomeFieldB
TableB - ID, SomeFieldB
TableAB-IDA,IDB,SomeFieldAB
TableAB - IDA, IDB, SomeFieldAB
我必须从头开始生成数据.因此,我将一些数据存储在TableA中,并将一些数据存储在TableB中.问题是,我现在需要将数据插入到TableAB中,而且我不知道如何生成一个脚本来将TableA和TableB的所有排列组合在一起.
I have to generate data from the ground up. So I've put some data in TableA, and I've put some data in TableB. The problem is, I need to insert data into TableAB now, and I don't know how to generate a script that will do all the permutations of TableA and TableB together.
例如:
表A有两条记录:
1,'A'
1, 'A'
2,"AA"
表B有两条记录:
3,"B"
3, 'B'
4,"B"
我想在TableAB中插入以下数据:
I want to insert the following data into TableAB:
1、3,第一烫"
1, 3, 'first perm'
1、4,第二个烫发"
1, 4, 'second perm'
2、3,第三烫发"
2, 3, 'third perm'
2,4,'第四烫发'
2, 4, 'fourth perm'
最简单的方法是什么?
推荐答案
不要使用 ancient 连接语法:FROM A, B
,请使用适当的连接:
Don't use the ancient join syntax: FROM A, B
, use a proper join:
DECLARE @TableA table (Col1 varchar(5), Col2 varchar(5))
DECLARE @Tableb table (Col1 varchar(5), Col2 varchar(5))
INSERT INTO @TableA VALUES ('a','a')
INSERT INTO @TableA VALUES ('aa','aa')
INSERT INTO @TableB VALUES ('b','b')
INSERT INTO @TableB VALUES ('BB','BB')
SELECT
*
FROM @TableA
CROSS JOIN @TableB
ORDER BY 1
输出
Col1 Col2 Col1 Col2
----- ----- ----- -----
a a b b
a a BB BB
aa aa b b
aa aa BB BB
(4 row(s) affected)
这也会产生相同的结果集:
this will also produce the same result set:
SELECT
*
FROM @TableA
JOIN @TableB ON 1=1
ORDER BY 1
这篇关于如何使用t-sql在两个完全不相关的表中获取每个排列的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!