-- DROP TABLE Table1, Table2, Table3, Table4, Table5
CREATE TABLE Table1(ID INT NOT NULL)
CREATE TABLE Table2(ID INT NOT NULL)
CREATE TABLE Table3(ID INT NOT NULL)
CREATE TABLE Table4(ID INT NOT NULL)
CREATE TABLE Table5(ID INT NOT NULL)
INSERT Table1
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3
INSERT Table2 SELECT ID FROM Table1
INSERT Table3 SELECT ID FROM Table1
INSERT Table4 SELECT ID FROM Table1
INSERT Table5 SELECT ID FROM Table1
SELECT a.Tbl1, a.ID1, b.Tbl2, b.ID2, b.ID3
FROM (
SELECT ID, 1 FROM Table1 UNION ALL
SELECT ID, 2 FROM Table2 UNION ALL
SELECT ID, 3 FROM Table3 UNION ALL
SELECT ID, 4 FROM Table4 UNION ALL
SELECT ID, 5 FROM Table5
) a(ID1, Tbl1)
JOIN (
SELECT a.ID, b.ID, 1 FROM Table1 a, Table1 b WHERE a.ID < b.ID UNION ALL
SELECT a.ID, b.ID, 2 FROM Table2 a, Table2 b WHERE a.ID < b.ID UNION ALL
SELECT a.ID, b.ID, 3 FROM Table3 a, Table3 b WHERE a.ID < b.ID UNION ALL
SELECT a.ID, b.ID, 4 FROM Table4 a, Table4 b WHERE a.ID < b.ID UNION ALL
SELECT a.ID, b.ID, 5 FROM Table5 a, Table5 b WHERE a.ID < b.ID
) b(ID2, ID3, Tbl2)
ON a.Tbl1 <> b.Tbl2 AND a.ID1 <> b.ID2 AND a.ID1 <> b.ID3
ORDER BY Tbl1, Tbl2, ID1
Резултат:
Tbl1 ID1 Tbl2 ID2 ID3
----------- ----------- ----------- ----------- -----------
1 1 2 2 3
1 2 2 1 3
1 3 2 1 2
1 1 3 2 3
1 2 3 1 3
1 3 3 1 2
1 1 4 2 3
1 2 4 1 3
1 3 4 1 2
1 1 5 2 3
1 2 5 1 3
1 3 5 1 2
2 1 1 2 3
2 2 1 1 3
2 3 1 1 2
2 1 3 2 3
2 2 3 1 3
2 3 3 1 2
2 1 4 2 3
2 2 4 1 3
2 3 4 1 2
2 1 5 2 3
2 2 5 1 3
2 3 5 1 2
3 1 1 2 3
3 2 1 1 3
3 3 1 1 2
3 1 2 2 3
3 2 2 1 3
3 3 2 1 2
3 1 4 2 3
3 2 4 1 3
3 3 4 1 2
3 1 5 2 3
3 2 5 1 3
3 3 5 1 2
4 1 1 2 3
4 2 1 1 3
4 3 1 1 2
4 1 2 2 3
4 2 2 1 3
4 3 2 1 2
4 1 3 2 3
4 2 3 1 3
4 3 3 1 2
4 1 5 2 3
4 2 5 1 3
4 3 5 1 2
5 1 1 2 3
5 2 1 1 3
5 3 1 1 2
5 1 2 2 3
5 2 2 1 3
5 3 2 1 2
5 1 3 2 3
5 2 3 1 3
5 3 3 1 2
5 1 4 2 3
5 2 4 1 3
5 3 4 1 2
(60 row(s) affected)
cheers,
</wqw>
Re: Обединяване на таблици [re: mousebg]
