如何添加新列并从其他列获取数据?SQL

You can see what I exactly mean on the image below. I want to create new column "Bill To This Company" and get the company name from BillToCustomerID

你可以在下面的图片上看到我的意思。我想创建一个新的列“Bill to This Company”，并从BillToCustomerID获取公司名称

4 个解决方案

#1

Could be you need a self join

你可能需要自我连接吗

select a.customerId, a.CustomerName, a.BillToCustomerId, b.CustmerName 
from my_table a 
inner join my_table  b on a.BillToCustomerId = b.customerId

#2

Just self-join on the table and give that CustomerName an alias

只需在表上进行自连接，并给CustomerName一个别名

select 
 c.CustomerId, 
 c.CustomerName, 
 c.BillToCustomerId, 
 b.CustomerName as "Bill To This Company"
from Customer c
left join Customer b on (b.CustomerId = c.BillToCustomerId)

#3

Seems you need subquery :

似乎需要子查询:

select c.*,
       (select c1.CustomerName
        from Customer c1
        where c1.BillToCustomerId = c.BillToCustomerId
        order by c1.customerid 
        limit 1
       ) as BilltoThisCompany
from Customer c;

#4

update tableName as t1 join tableName as t2 on t1.BillToCustomerID=t2.CustomerId
  set t1.`Bill To This Company` = t2.customerName;

#1