mysql - 在sql中查找每个公司最旧的帐户

Question

我有一些如下所示的表格

用户

-----------------------------------------
| id | policyId | createdAt | updatedAt |
-----------------------------------------
| 1  |    1     |  2017/8/5 |  2017/8/5 |
| 2  |    1     |  2016/4/5 |  2017/8/5 |
| 3  |    2     |  2017/7/2 |  2017/8/5 |
| 4  |    2     |  2018/8/5 |  2017/8/5 |
-----------------------------------------

政策

------------------------------------------
| id | companyId | createdAt | updatedAt |
------------------------------------------
| 1  |    1      |  2017/8/5 |  2017/8/5 |
| 2  |    2      |  2016/4/5 |  2017/8/5 |
------------------------------------------

公司

-----------------------------------------
| id | policyId | createdAt | updatedAt |
-----------------------------------------
| 1  |    2     |  2017/8/5 |  2017/8/5 |
| 2  |    1     |  2016/4/5 |  2017/8/5 |
-----------------------------------------

我需要回答这个问题“拥有最旧帐户的每个公司的用户 ID 是什么。因此输出应如下所示。

输出

----------------------------------
| CompanyId | UserId | CreatedAt |
----------------------------------
|     1     |   2    |  2016/4/5 |   
|     2     |   3    |  2017/7/2 |
----------------------------------

<小时/>

到目前为止，我得到的看起来像这样，但我知道它几乎是正确的。

SELECT c.id, MIN(u.createdAt) FROM companies as c
    JOIN policies as p on p.companyId = c.id
    JOIN users as u on u.policyId = p.id
    GROUP BY c.id;

这似乎让我获得每个公司用户的最旧日期，但我不确定如何将用户与该日期关联起来以获取用户 ID。我认为上面的查询可能必须是一个子查询，但就我的 SQL 知识而言，这就是了。

如有任何帮助，我们将不胜感激。

Answer 1

我需要将整个查询连接到自身，并通过 comapny id 和createdAt 列连接。

在此处查看演示:http://sqlfiddle.com/#!9/6f4ea7/22

SELECT c.id as companyID,
  u.id as userID,
  u.createdAt
FROM companies as c
JOIN policies as p on p.companyId = c.id
JOIN users as u on u.policyId = p.id
JOIN (SELECT c.id as companyID, 
    min(u.createdAt) as min_dt
    FROM companies as c
    JOIN policies as p on p.companyId = c.id
    JOIN users as u on u.policyId = p.id
    GROUP BY c.id) sub
on c.id=sub.companyID 
where u.createdAt=sub.min_dt