连接表(没有子句)

Question

我想要连接到表并获得以下输出

Table1

TestId1
----------
one
two
three
four
five
six
seven
eight

Table2

TestId2
----------
fiftythree
fiftyfour
fiftytwo
fiftyfive
fiftyone

我希望Table3作为我的输出，包括来自table1的所有行和来自table2的第一行，直到没有剩下的行，然后它们应该开始重复。

作为另一个答案，他们也可以被随机分配。

TestId1        TestId2   
----------     ----------
one           fiftythree
two           fiftyfour 
three         fiftytwo  
four          fiftyfive 
five          fiftyone  
six           fiftythree
seven         fiftyfour 
eight         fiftytwo  

Answer 1

试试这个：

SELECT t1.name, t2.name FROM
(
    SELECT (ROW_NUMBER() OVER(ORDER BY name)-1)%(SELECT COUNT(*) FROM test2) AS j,* 
    FROM test1
) t1
INNER JOIN 
(
    SELECT ROW_NUMBER() OVER(ORDER BY name)-1 AS j,* 
    FROM test2
) t2 ON t1.j = t2.j
ORDER BY t1.name

详情如下：

SELECT (ROW_NUMBER() OVER(ORDER BY name)-1) AS j,* 
FROM test1

返回：

0 | one
1 | two
2 | three
3 | four
4 | five
5 | six
6 | seven
7 | eight

这是：

SELECT ROW_NUMBER() OVER(ORDER BY name)-1 AS j,* 
FROM test2

返回：

0 | fiftythree
1 | fiftyfour
2 | fiftytwo
3 | fiftyfive
4 | fiftyone

您所要做的就是将最长表的第一列除以较短的元素数(% --我不知道它的英文名称)：

SELECT (ROW_NUMBER() OVER(ORDER BY name)-1)%(SELECT COUNT(*) FROM test2) AS j,* 
FROM test1

返回：

0 | one
1 | two
2 | three
3 | four
4 | five
0 | six
1 | seven
2 | eight

现在所要做的就是将第一列上的两个表连接起来。

这个解决方案只使用一个查询，但它假设在table1中有比在table2中更多的元素。如果您不喜欢这个解决方案，我只是给了您编写存储过程的良好基础。

Answer 2

你的替代解决方案是唯一的

SELECT
    TestID1,
    TestID2
FROM
    (SELECT COUNT(*) AS Count1 FROM Table1) C1 --one row
    CROSS JOIN
    (SELECT COUNT(*) AS Count2 FROM Table2) C2 --one row
    CROSS JOIN
    (
    SELECT
        ROW_NUMBER() OVER (ORDER BY TestID1) AS Rank1,
        TestID1,
    FROM
        Table1
    ) t1
    JOIN
    (
    SELECT
        ROW_NUMBER() OVER (ORDER BY TestID1) AS Rank2,
        TestID2,
    FROM
        Table2
    ) t2 ON
         t1.Rank1 % CASE WHEN C1.Count1 > C2.Count2 THEN C2.Count2 ELSE 2000000000 END
         =
         t2.Rank2 % CASE WHEN C2.Count2 > C1.Count1 THEN C1.Count1 ELSE 2000000000 END
ORDER BY
    t1.Rank1, t2.Rank2

Answer 3

这里可以工作，并且只有一个游标：

if exists(select object_id('tempdb..#TestId1'))
drop table #TestId1

if exists(select object_id('tempdb..#TestId2'))
drop table #TestId2

if exists(select object_id('tempdb..#result'))
drop table #result

create table #TestId1(col_1 varchar(100))

create table #TestId2(col_2 varchar(100))

create table #result (col_1 varchar(100), col_2 varchar(100))

set rowcount 0

insert into #TestId1(col_1 )
select col='one'
union all select col='two'
union all select col='three'
union all select col='four'
union all select col='five'
union all select col='six'
union all select col='seven'
union all select col='eigh'

insert into #TestId2(col_2 )
select col='fiftythree'
union all select col='fiftyfour'
union all select col='fiftytwo'
union all select col='fiftyfive'
union all select col='fiftyone'

DECLARE @sectblcnt int
select @sectblcnt=count(*) from #TestId2

DECLARE @sectableNo int

DECLARE @rowno int

declare @col_1 varchar(100), @col_2 varchar(100)

set @rowno=0

DECLARE curs CURSOR FOR SELECT col_1 FROM #TestId1

OPEN curs

FETCH NEXT FROM curs INTO @col_1

WHILE @@FETCH_STATUS = 0
BEGIN
set @rowno=@rowno+1;

set @sectableNo = @rowno % @sectblcnt
set rowcount @sectableNo

select @col_2=col_2 from #TestId2

insert into #result(col_1, col_2)
    values(@col_1, @col_2)

FETCH NEXT FROM curs 
INTO @col_1
END 

CLOSE curs

DEALLOCATE curs

set rowcount 0

select * from #result