将2个表的选择性行放入第3个表中

[Edit:] I Edited My Question ; I Put Up Same Column Name initially in three tables..So question was becoming Confusing ..Apologize for That

[编辑:]我编辑了我的问题;我最初在三个表中放置了相同的列名。所以问题变得令人困惑..为此进行了道歉

I have 2 tables :

我有2张桌子:

Table_1

[1stColumn] [2nd Column]
1            11
2            12

and Table_2

[1stColumn] [2nd Column]
1            21
2            22

What I want to show data like this

我想要显示这样的数据

Third Table

[1stColumn] [2nd Column]
1            11            -----> (1st Row of 1st Table)
2            22            -----> (2nd Row of 2nd Table)

I have Tried this :

我试过这个:

select t1.c2,t2.c2 from Table_1 t1 join Table_2 t2 on t1.c1=t2.c1 
where t1.c2='11' and t2.c2='22'

This wont work ..i Also Tried other join but that doesnt result what is required ...

这不会工作..i也试过其他加入,但不会产生所需要的...

Any Suggestion would be Helpful

任何建议都会有所帮助

2 个解决方案

#1

I think you want union all:

我想你想要结合所有人:

select t1.c1, t1.c2 from Table_1 t1 where t1.c2 = '11'
union all
select t2.c1, t2.c2 from Table_2 t2 where t2.c2 = '22';

You can insert the values into a third table using insert.

您可以使用insert将值插入第三个表。

#2

I just realized I misread your question. My eyes somehow just blithely interpreted 'row' as 'col'. I think I'm smelling what you're stepping in now. You could use modulo % to alternate selected rows while using UNION ALL inside an INSERT. It seems pretty complicated for such a simple task, but this is the only way I could figure how to make it alternate from table_1 to table_2 even when c1 (which I'm assuming is your primary key) is non-contiguous. I'm sure this can be streamlined somehow by someone smarter than me.

我才意识到我误解了你的问题。我的眼睛莫名其妙地只是将'排'解释为'col'。我想我现在正在嗅到你正在踩的东西。在INSERT中使用UNION ALL时,可以使用modulo%来交替选定的行。对于这么简单的任务来说这似乎很复杂,但这是我能想出如何使它从table_1到table_2交替的唯一方法,即使c1(我假设是你的主键)是非连续的。我确信这可以通过比我更聪明的人来简化。

INSERT INTO table_3 (c1,c2)
SELECT * FROM (
  SELECT rownum, c2 
  FROM ( 
    SELECT 
        @row := @row +1 AS rownum, c2 
    FROM ( SELECT @row :=0) r, table_1 
  ) ranked 
  WHERE rownum % 2 = 1
  UNION ALL
  SELECT rownum, c2 
  FROM ( 
    SELECT @row := @row +1 AS rownum, c2 
    FROM ( SELECT @row :=0) r, table_2 
  ) ranked 
  WHERE rownum % 2 = 0) AS dt;

Here is a fiddle, note the deletion of a table_2 record

这是一个小提琴,请注意删除table_2记录

#1