运行时未知但定义的N个数据集的交叉连接
有没有一种方法可以使用SQL语句对表中的数据集进行分区，然后交叉连接它们？我目前必须在C#中进行处理，但需要很长时间才能处理。
为了举例说明，我有一个包含以下项目的测试表。
A1..A4、B1..B3、C1..C5表示我正在处理的3个分区。请注意，这些分区仅用于说明。我不知道表中有多少分区，除非我必须手动对它们进行分组。
我想对所有三个分区(A、B、C)进行交叉连接，以便最终结果如下：
我知道在某个时候必须使用递归，但我不知道如何处理它。
回答 2
Stack Overflow用户
发布于 2019-05-09 15:50:03
这类事情可以通过递归查询来完成，尽管80+ x-联接级别是相当难以想象的数据量。我将在下面的示例中生成一个逗号分隔的字符串集，每个记录代表贯穿交叉连接的一条路径。
考虑到示例数据的性质，该示例将使用每个记录中的所有字母字符作为记录的分区(如果实际数据与示例模式不同，则需要对此进行调整)。
首先，创建测试数据：
CREATE TABLE TEST_TABLE(ITEM VARCHAR2(32) NOT NULL PRIMARY KEY );
INSERT INTO TEST_TABLE VALUES ('A1');
INSERT INTO TEST_TABLE VALUES ('A2');
INSERT INTO TEST_TABLE VALUES ('A3');
INSERT INTO TEST_TABLE VALUES ('A4');
INSERT INTO TEST_TABLE VALUES ('B1');
INSERT INTO TEST_TABLE VALUES ('B2');
INSERT INTO TEST_TABLE VALUES ('B3');
INSERT INTO TEST_TABLE VALUES ('C1');
INSERT INTO TEST_TABLE VALUES ('C2');
INSERT INTO TEST_TABLE VALUES ('C3');
INSERT INTO TEST_TABLE VALUES ('C4');
INSERT INTO TEST_TABLE VALUES ('C5');
COMMIT;然后查询。此时，预计将有60行记录，因为在“A”、“B”、“C”组中有4*3*5条记录。
下面的示例机械地将记录按照字母前缀(在提供的数据中，分为'A‘、'B’、'C‘分区)。
然后，它将(任意)以(字母顺序)第一个分区(在本例中为‘A’，尽管它是动态确定的)开始，并递归地将该分区中的每个记录连接到(字母顺序)下一个分区中的所有记录(在本例中为‘B’)，创建两个节点路径，并在这样做时将记录数乘以。
然后，对每个两个节点路径重复相同的进程，进入下一个分区(在本例中为‘C’)，在该分区被耗尽并停止。然后，只接受那些表示从根到叶的完整树的路径(本例中的三个节点)。
WITH PARTITIONED_RECORD AS (
SELECT ITEM,
DENSE_RANK() OVER (ORDER BY REGEXP_SUBSTR(ITEM, '^[A-Z]{1,}') ASC) AS PARTITION_SORT,
ROW_NUMBER() OVER (ORDER BY ITEM ASC NULLS LAST) AS ABSOLUTE_SORT
FROM TEST_TABLE),
RAW_GRAPH AS (
SELECT SYS_CONNECT_BY_PATH(ITEM,',' ) AS CONNECTION_PATH,
CONNECT_BY_ISLEAF AS LEAF_MARKER
FROM PARTITIONED_RECORD
START WITH PARTITION_SORT = 1
CONNECT BY PARTITION_SORT = PRIOR PARTITION_SORT + 1
AND ABSOLUTE_SORT > PRIOR ABSOLUTE_SORT)
SELECT REGEXP_REPLACE(CONNECTION_PATH,'^,',NULL) AS CROSS_JOINED_SET
FROM RAW_GRAPH
WHERE LEAF_MARKER = 1
ORDER BY 1 ASC;结果：
CROSS_JOINED_SET
A1,B1,C1
A1,B1,C2
A1,B1,C3
A1,B1,C4
A1,B1,C5
A1,B2,C1
...
A4,B3,C3
A4,B3,C4
A4,B3,C5
60 rows selected.然后使用其他分区进行测试。首先，向新的parititon添加一个元素。
INSERT INTO TEST_TABLE VALUES ('D1');和重新查询(仍预期有60行，因为x-联接中没有展开)：
CROSS_JOINED_SET
...
A4,B3,C2,D1
A4,B3,C3,D1
A4,B3,C4,D1
A4,B3,C5,D1
60 rows selected.并向'D‘分区添加第二个元素，并重新查询(现在需要120条记录)：
INSERT INTO TEST_TABLE VALUES ('D2');结果：
CROSS_JOINED_SET
A1,B1,C1,D1
A1,B1,C1,D2
A1,B1,C2,D1
A1,B1,C2,D2
...
A4,B3,C4,D1
A4,B3,C4,D2
A4,B3,C5,D1
A4,B3,C5,D2
120 rows selected.它和第五组增加了两项记录(预期为240行)：
INSERT INTO TEST_TABLE VALUES ('EJY1017');
INSERT INTO TEST_TABLE VALUES ('EJY1018');结果：
CROSS_JOINED_SET
A1,B1,C1,D1,EJY1017
A1,B1,C1,D1,EJY1018
A1,B1,C1,D2,EJY1017
A1,B1,C1,D2,EJY1018
...
A4,B3,C5,D1,EJY1017
A4,B3,C5,D1,EJY1018
A4,B3,C5,D2,EJY1017
A4,B3,C5,D2,EJY1018
240 rows selected.编辑:在此基础上添加另一个变体，使用动态交叉联接.
另一种方法是在动态sql中生成x-联接。下面的示例再次返回逗号分隔的字符串，但通过CROSS JOIN语句返回。
-创建返回类型(没有必要)
CREATE OR REPLACE TYPE STRINGS IS TABLE OF VARCHAR2(4000);
/-和一个检测不同组并为每个组进行交叉连接的函数。
CREATE OR REPLACE FUNCTION XJOIN RETURN STRINGS
IS
V_HEADER CHARACTER VARYING(512 BYTE) := ' SELECT ';
V_XJOINS CHARACTER VARYING(32000 BYTE) := NULL;
V_BUCKETS STRINGS := STRINGS();
V_RESULTS STRINGS := STRINGS();
BEGIN
SELECT DISTINCT REGEXP_SUBSTR(ITEM,'^[A-Z]{1,}')
BULK COLLECT INTO V_BUCKETS
FROM TEST_TABLE ORDER BY 1 ASC;
FOR BUCKET_INDEX IN 1..V_BUCKETS.COUNT
LOOP
IF BUCKET_INDEX > 1 THEN
V_HEADER := V_HEADER||'||CHR(44)||';
V_XJOINS := V_XJOINS||' CROSS JOIN ';
END IF;
V_HEADER := V_HEADER||V_BUCKETS(BUCKET_INDEX);
V_XJOINS := V_XJOINS || UTL_LMS.FORMAT_MESSAGE(Q'! (SELECT ITEM AS %s FROM TEST_TABLE WHERE ITEM LIKE '%s%') !',V_BUCKETS(BUCKET_INDEX),V_BUCKETS(BUCKET_INDEX));
END LOOP;
EXECUTE IMMEDIATE (V_HEADER||' FROM '||V_XJOINS) BULK COLLECT INTO V_RESULTS;
RETURN V_RESULTS;
END;
/-并称之为：
SELECT COLUMN_VALUE FROM TABLE ( XJOIN());
...
A2,B3,C5,D2,EJY1018
A3,B3,C5,D2,EJY1018
A4,B3,C5,D2,EJY1018
240 rows selected.Stack Overflow用户
发布于 2019-05-07 21:16:52
这是TSQL，但它可能帮助您在ORACLE中创建类似的内容。
WITH DATA_temp as (
SELECT 'A1' ID UNION
SELECT 'A2' ID UNION
SELECT 'B1' ID UNION
SELECT 'B2' ID UNION
SELECT 'D1' ID UNION
SELECT 'D2' ID UNION
SELECT 'c1' ID UNION
SELECT 'c2' ID
)
SELECT ID, DENSE_RANK()OVER(ORDER BY LEFT(ID,1)) AS RN
INTO #TEMP
FROM DATA_temp;
DECLARE
@X varchar(2) = '1'
,@TB varchar(2)
,@SQL AS VARCHAR(MAX)
WHILE @X <= (SELECT MAX(RN) FROM #TEMP)
BEGIN
SET @TB = (SELECT DISTINCT LEFT(ID,1) ID FROM #TEMP WHERE rn = @X)
SET @SQL = ISNULL(@SQL,'') + '(SELECT ID FROM #TEMP WHERE rn ='+ @X+' ) AS '+@TB+' CROSS JOIN
'
SET @X = @X + 1
END
SET @SQL = 'SELECT * FROM '+ LEFT(@SQL,LEN(@SQL) - 13)
EXEC(@SQL)https://stackoverflow.com/questions/56030014
复制相似问题
腾讯云开发者
