php - 将 3 个表的结果与 MAX(date) 合并

Question

我尝试使用 sql 连接合并 3 个表，同时使用 MAX(date) 从其中 2 个表中提取最新记录。

我已经使用 INNER JOINS 和 LEFT JOINS 尝试了几种不同的查询，但尚未得出预期的结果

SELECT clients.name, clients.company, latest_jobs.contractor, latest_jobs.job_type, latest_jobs.last_date, sales_calls.last_sales_date, sales_calls.client
FROM
 (SELECT
   contractor,
   MAX(start_date) AS last_date,
   job_type,
  FROM jobs
  GROUP BY contractor) AS latest_jobs
INNER JOIN clients ON latest_jobs.contractor = clients.name
(SELECT
   MAX(sales_date) AS last_sales_date,
   client,
  FROM sales_calls 
  GROUP BY client) AS latest_jobs
 LEFT JOIN sales_calls ON sales_calls.client = clients.name
ORDER BY clients.name

客户表

|-------------------| 
| name              | 
|-------------------|
| client 1          |        
|-------------------|
| client 2          |   
|-------------------|
| client 3          |         
|-------------------|

职位表

|-------------------|---------|-------------| 
| job_type          | company | start_date  |
|-------------------|---------|-------------|
| job_type 1        |client 1 | 2019-02-01  |
|-------------------|---------|-------------|
| job_type 3        |client 2 | 2019-02-02  |
|-------------------|---------|-------------|
| job_type 2        |client 3 | 2019-02-03  |
|-------------------|---------|-------------| 
| job_type 4        |client 2 | 2019-02-05  |
|-------------------|---------|-------------|
| job_type 2        |client 1 | 2019-01-18  |
|-------------------|---------|-------------|
| job_type 3        |client 2 | 2019-01-15  |
|-------------------|---------|-------------|
| job_type 2        |client 3 | 2019-02-08  |
|-------------------|---------|-------------|

销售表

|---------|-------------| 
| company | sales_date  |
|---------|-------------|
|client 1 | 2019-02-01  |
|---------|-------------|
|client 2 | 2019-02-02  |
|---------|-------------|
|client 3 | 2019-02-03  |
|---------|-------------| 
| client 2 | 2019-02-05 |
|---------|-------------|
|client 1 | 2019-01-18  |
|---------|-------------|
|client 2 | 2019-01-15  |
|---------|-------------|
|client 3 | 2019-02-08  |
|-----------------------|

预期结果

|----------|-----------------|-----------------|-----------------|
| Name     |  last_job_date  | last_job_type   | last_sales_date |
|----------|-----------------|-----------------|-----------------|
| client 1 |  2019-02-01     | job_type 1      |  2019-02-01     |
|----------|-----------------|-----------------|-----------------|
| client 2 |  2019-02-05     | job_type 4      |  2019-02-05     |
|----------|-----------------|-----------------|-----------------|
| client 3 |  2019-02-02     | job_type 3      |  2019-02-08     |
|----------|-----------------|-----------------|-----------------|

Answer 1

规范根本不明确。我们在客户表中看到对 name 和 company 列的引用，以及引用名为 contracor 的列的示例查询，我们没有看到该列的定义。所以我们对表之间关系的理解有点困惑。

所以我们只是猜测。但这里是对我们想要实现的目标的猜测。

SELECT c.name
     , c.company
     , j.contractor
     , j.job_type
     , j.start_date       AS last_job_date
     , s.last_sales_date
     , s.client
  FROM clients c

  LEFT
  JOIN ( SELECT ls.client
              , MAX(ls.sales_date) AS last_sales_date
           FROM sales_calls ls
          GROUP
             BY ls.client
       ) s
    ON s.client = c.name

  LEFT
  JOIN ( SELECT llj.company
              , MAX(llj.start_date) AS last_start_date
           FROM jobs llj
          GROUP
             BY llj.company
       ) lj
    ON lj.company = c.company

  LEFT
  JOIN jobs j
    ON j.company    = lj.company
   AND j.start_date = lj.last_start_date

 ORDER
    BY c.name

<小时/>

我们从客户获取所有行，并按名称排序。

我们使用内联 View 来获取每个客户的最新 sales_date，并将该结果(作为外部联接)连接到客户表中的行。

同样，我们在内联 View 中从每个公司的职位表中获取最新的开始日期。该结果将连接到客户表中的行。

我们还连接回 jobs 表，从具有最新 start_date 的行上的列中获取值。 (如果同一公司和 start_date 有多行，那么这有可能返回多行，并且有一些方法可以处理该问题。目前，我们的查询假设 (company,start_date) 元组是唯一的.