mysql - 将列(来自其他表)添加到 'most recent notes for each user' 查询 sql

Question

我有下表:[运行代码片段]

<!--__________________________________________________-->
---------------------'notes' table-----------------------
<!--__________________________________________________-->
<table>

  <thead>
    <th>note_id</th>
    <th>note_date</th>
    <th>note_time</th>
    <th>writer_id</th>
  </thead>

  <tbody>
    <tr>
      <td>1</td>
      <td>2-05-2016</td>
      <td>08:37:05</td>
      <td>1</td>
    </tr>
    <tr>
      <td>2</td>
      <td>14-05-2016</td>
      <td>11:44:01</td>
      <td>2</td>
    </tr>
    <tr>
      <td>3</td>
      <td>14-05-2016</td>
      <td>07:57:35</td>
      <td>2</td>
    </tr>
    <tr>
      <td>4</td>
      <td>26-05-2016</td>
      <td>15:02:22</td>
      <td>3</td>
    </tr>
    <tr>
      <td>5</td>
      <td>27-05-2016</td>
      <td>11:39:04</td>
      <td>4</td>
    </tr>
  </tbody>

</table>


<!--__________________________________________________-->
---------------------'writers' table-----------------------
<!--__________________________________________________-->

<table>

  <thead>
    <th>writer_id</th>
    <th>First Name</th>
    <th>Last Name</th>
  </thead>

  <tbody>
    <tr>
      <td>1</td>
      <td>John</td>
      <td>Doe</td>
    </tr>
    <tr>
      <td>2</td>
      <td>Jane</td>
      <td>Doe</td>
    </tr>
    <tr>
      <td>3</td>
      <td>Jame</td>
      <td>Doe</td>
    </tr>
    <tr>
      <td>4</td>
      <td>Lame</td>
      <td>Doe</td>
    </tr>
    <tr>
      <td>5</td>
      <td>Shame</td>
      <td>Doe</td>
    </tr>
  </tbody>

</table>

我想以以下格式仅显示最新的笔记:

<table>

  <thead>
    <th>First Name</th>
    <th>Last Name</th>
    <th>note_date</th>
    <th>note_time</th>
  </thead>

  <tbody>
    <tr>
      <td>John</td>
      <td>Doe</td>
      <td>2-05-2016</td>
      <td>08:37:05</td>
      <td>1</td>
    </tr>
    <tr>
      <td>Jane</td>
      <td>Doe</td>
      <td>14-05-2016</td>
      <td>11:44:01</td>
      <td>2</td>
    </tr>
    <tr>
      <td>Jame</td>
      <td>Doe</td>
      <td>26-05-2016</td>
      <td>15:02:22</td>
      <td>3</td>
    </tr>
    <tr>
      <td>Lame</td>
      <td>Doe</td>
      <td>27-05-2016</td>
      <td>11:39:04</td>
      <td>4</td>
    </tr>
  </tbody>

</table>

我目前有以下 SQL 查询，可以正确返回最新的笔记(尽管我收到了 Jane Doe 的两个笔记，作者 id 2，因为笔记是在同一日期):

注意:我暂时不关心重复项。

SELECT a.note_id, a.note_date, a.note_time
FROM notes a
INNER JOIN (
    SELECT note_id, MAX(note_date) note_date
    FROM notes
    GROUP BY note_id
) b ON a.note_id = b.note_id AND a.note_date = b.note_date

我想将作者姓名添加到查询中，但不知道如何添加连接:

(notes INNER JOIN writers ON notes.writer_id = writers.writer_id)

Answer 1

这是添加JOIN的一种方法:

SELECT w.name, n.*
FROM notes n INNER JOIN
     (SELECT note_id, MAX(note_date) note_date
      FROM notes
      GROUP BY note_id
     ) nn
     ON n.note_id = nn.note_id AND n.note_date = nn.note_date INNER JOIN
     writers w
     ON n.writer_id = w.writer_id;