mysql - 从 LEFT JOINT 中仅选择一个 +mySQL

Question

在我的 SQL 数据库中有以下表(简化):

表格博客:

+----+----------------------+----------+
| ID |         Date         | TitleGer |
+----+----------------------+----------+
| 1  | 2017-04-28 15:09:46  | Huhu     |
| 2  | 2017-04-28 15:16:18  | Miau     |
| 3  | 2017-04-28 15:17:14  | Kleff    |
+----+----------------------+----------+

表图片加入:

+-------------+---------+---------------------+
|  IDPicture  | IDBlog  |        Date         |
+-------------+---------+---------------------+
|         86  |      1  | 2017-06-28 17:41:11 |
|         87  |      1  | 2017-06-28 17:41:11 |
+-------------+---------+---------------------+

表格图片:

+------+-------------------------+---------------------+
|  ID  |        Filename         |        Date         |
+------+-------------------------+---------------------+
|  86  | 20170512200326_320.jpg  | 2017-05-12 20:03:26 |
|  87  | 20170512200326_384.jpg  | 2017-05-12 20:03:30 |
+------+-------------------------+---------------------+

PictureJoin 将图片与博客表“连接”在一起。现在我使用以下 SQL 命令将这两个表 (Blog - PictureJoin)/(PictureJoin - Pictures) 连接在一起。

SELECT
  Blogs.ID,
  Blogs.Date,
  TitleGer,
  Pictures.Filename
FROM
  Blogs
LEFT JOIN
  PicturesJoin ON PicturesJoin.IDBlog = Blogs.ID
LEFT JOIN
  Pictures ON Pictures.ID = PicturesJoin.IDPicture
ORDER BY
  DATE DESC

结果可能如下所示:

+------+----------------------+-----------+------------------------+
|  ID  |        Date          | TitleGer  |       Filename         |
+------+----------------------+-----------+------------------------+
|   1  | 2017-06-28 15:09:46  | Huhu      | 20170512200326_320.jpg |
|   1  | 2017-06-28 15:09:46  | Huhu      | 20170512200326_384.jpg |
|   2  | 2017-04-28 15:16:18  | Miau      | NULL                   |
|   3  | 2017-04-28 15:17:14  | Kleff     | NULL                   |
+------+----------------------+-----------+------------------------+

他用可用的图片制作了叉积，这也是合乎逻辑的。但我希望他只使用他找到的第一张图片。最后它应该看起来像这样:

+------+----------------------+-----------+------------------------+
|  ID  |        Date          | TitleGer  |       Filename         |
+------+----------------------+-----------+------------------------+
|   1  | 2017-06-28 15:09:46  | Huhu      | 20170512200326_320.jpg |
|   2  | 2017-04-28 15:16:18  | Miau      | NULL                   |
|   3  | 2017-04-28 15:17:14  | Kleff     | NULL                   |
+------+----------------------+-----------+------------------------+

尝试了几个小时，但无法正常工作。请帮忙!

Answer 1

最简单的方法可能是仅从 PicturesJoin 中为每个 IDBlog 选择一个 IDPicture:

SELECT
  b.ID,
  b.Date,
  b.TitleGer,
  p.Filename
FROM Blogs b
LEFT JOIN
(
  SELECT 
    IDBlog, 
    MIN(IDPicture) AS IDPicture
  FROM PicturesJoin 
  GROUP BY IDBlog
) pj ON pj.IDBlog = b.ID
LEFT JOIN Pictures p ON p.ID = pj.IDPicture
ORDER BY b.Date DESC;