我有两个这样的表:

  New_Customer     Order_Id     Invoice
  Aaron            5             5
  John             56            44
  --------------------------
  Old_Customers    Order_Id     Ticket_Num
  Casey            44            11
  Jonathan          1            4


我想要的是?

  All_Customers    All_Order_Ids
  Aaron            5
  John             56
  Casey            44
  Jonathan         1


我能否仅使用一个select语句获得这两个列的并集(没有其他列)?

我试过了

   select tb1.New_Customer union tb2.Old_Customers as All_Customers, tb1.Order_Id union tb2.Order_Id as All_Order_Ids;


但是语法错误。有人可以告诉我我在做什么错吗?

最佳答案

使用第一个语句中显示的列别名执行2个select语句的UNION。这些别名将被提取并用于结果集中。

create table t1
(   new_customer varchar(30) not null,
    order_id int not null,
    invoice int not null
);
insert t1 values ('aaron',5,5),('john',56,44);

create table t2
(   old_customers varchar(30) not null,
    order_id int not null,
    ticket_num int not null
);
insert t2 values ('casey',44,11),('jonathan',1,4);


查询:

select new_customer as All_Customers,order_id as All_Order_Ids from t1
union
select old_customers,order_id from t2

+---------------+---------------+
| All_Customers | All_Order_Ids |
+---------------+---------------+
| aaron         |             5 |
| john          |            56 |
| casey         |            44 |
| jonathan      |             1 |
+---------------+---------------+

关于mysql - 从多列合并,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/32280430/

10-08 23:24