php - mysql 从 T1 选择(如果不在 T2 中)，否则替换

Question

所以我试图编写的 mysql PDO 查询必须比较两个表和输出数组中的列。

1. 如果表1的输出数据为空
2. 如果单元格的值与表2相同， 首先输出该值，然后输出所有其他值。

工作表:

table1
|jobName|Name|
|job1   |John|
|job2   |John|
|job3   |John|
|jobABC |Jack|
|jobCCC |Jack|
|jobXYZ |Jack|

和日期表:

table2
|day |jobName|Name|
|day1|       |John|
|day1|jobCCC |Jack|
|day2|job2   |John|
|day2|       |Jack|
|day3|jobXYZ |Jack|
|day3|job1   |John|

John 的输出应该是:

day1, job1, job2, job3
day2, job2, job1, job3
day3, job1, job2, job3

Jack 的输出应该是:

day1, jobCCC, jobABC, jobXYZ
day2, jobABC, jobCCC, jobXYZ
day3, jobXYZ, jobABC, jobCCC

谢谢!

Answer 1

我已经很接近了...有人愿意帮助我吗？

http://sqlfiddle.com/#!9/e5a510/12

SELECT 
  b.Day,
  COALESCE(a.Name, b.Name) AS Name, 
  COALESCE(a.JobName, b.JobName) AS JobName
FROM table1 a, table2 b
WHERE a.Name = b.Name

更新!

这是最终产品(我认为):

http://sqlfiddle.com/#!9/e5a510/53

SELECT 
  b.Day,
  COALESCE(a.Name, b.Name) AS Name, 
  COALESCE(a.JobName, b.JobName) AS JobName
FROM table1 a, table2 b
WHERE a.Name = b.Name AND a.Name = 'Jack' 
ORDER BY   b.Day ASC, a.JobName = b.JobName DESC, a.JobName ASC