合并2个表，每个表具有不同的数据 [英] Combine 2 Tables with different data on each

问题描述

如何合并两个具有不同数据的表，并为表2中的所有数据设置CompanyC的值，而表1中的CompanyD没有关系.我希望Company C设置表2中的所有数据.

How can I combine two tables with different data and set value for CompanyC to all data in Table 2 and CompanyD has no relationship on Table 1. I want the Company C to set all data on Table 2.

   T1:                                 T2:

company     jobs                        emp_id  company     jobs     emp_name
-----------------------------------      -------------------------------------
CompanyA     IT                          1      CompanyA     IT        John
CompanyB     Business                    2      CompanyB     Business  Mike
CompanyC     Engineer                    3      CompanyD     Nurse     Mitch

结果表如下:

emp_id     company      jobs            emp_name           
----------------------------------------------     
1          CompanyA     IT                John     
1          CompanyC     Engineer          John  
2          CompanyB     Business          Mike      
2          CompanyC     Engineer          Mike
3          CompanyD     Nurse             Mitch
3          CompanyC     Engineer          Mitch

这是我尝试过的

 SELECT  t2.emp_id, coalesce(t1.company_name, t2.company_name) AS company_name, 
    coalesce(t1.jobs, t2.jobs) AS jobs, 
    t2.emp_name, 
FROM Table1 t1  
    FULL OUTER JOIN      
    Table2 t2 ON t2.company = t1.company AND t2.jobs = t1.jobs

推荐答案

您可以为Table1中存在的公司创建表的INNER JOIN，然后为Table2中存在的公司进行UNION ALL Table1中不存在:

You can do an INNER JOIN of the tables for the companies that exist in Table1 and then UNION ALL for the companies of Table2 that do not exist in Table1:

SELECT t2.emp_id, t1.company, t1.jobs, t2.emp_name
FROM Table1 t1 INNER JOIN Table2 t2
ON t2.company = t1.company
OR NOT EXISTS (SELECT 1 FROM Table2 WHERE company = t1.company)
UNION ALL
SELECT t2.*
FROM Table2 t2
WHERE NOT EXISTS (SELECT 1 FROM Table1 WHERE company = t2.company)

您的问题尚不清楚，您是否必须仅在company或jobs上同时链接这两个表，在这种情况下，您应该使用此查询:

It is not clear in your question if you the 2 tables must be linked only on company or on jobs also, in which case you should use this query:

SELECT t2.emp_id, t1.company, t1.jobs, t2.emp_name
FROM Table1 t1 INNER JOIN Table2 t2
ON t2.company = t1.company  AND t2.jobs = t1.jobs
OR NOT EXISTS (SELECT 1 FROM Table2 WHERE company = t1.company AND jobs = t1.jobs)
UNION ALL
SELECT t2.*
FROM Table2 t2
WHERE NOT EXISTS (SELECT 1 FROM Table1 WHERE company = t2.company AND jobs = t2.jobs)

请参见演示.
结果:

See the demo.
Results:

emp_id | company  | jobs     | emp_name
-----: | :------- | :------- | :-------
     1 | CompanyA | IT       | John    
     1 | CompanyC | Engineer | John    
     2 | CompanyB | Business | Mike    
     2 | CompanyC | Engineer | Mike    
     3 | CompanyC | Engineer | Mitch   
     3 | CompanyD | Nurse    | Mitch  

这篇关于合并2个表，每个表具有不同的数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！