我有三个表想要加入。
第一个是"用户"表。
+---------+--------------+---------+
|Id |UserName |blah |
+---------+--------------+---------+
|1 |Amila |blah |
+---------+--------------+---------+
|2 |Kamal |blah |
+---------+--------------+---------+
|3 |Nuwan |blah |
+---------+--------------+---------+
第二个是"图像"表。它包含用户上传的所有图像。此外,如果 Type==PI 和最大 id 将是用户的当前个人资料图像。
+---------+--------------+---------+---------+
|Id |Image |Type |User |
+---------+--------------+---------+---------+
|1 |12358.jpg |PI |1 |
+---------+--------------+---------+---------+
|2 |12589.jpg |PI |2 |
+---------+--------------+---------+---------+
|3 |45862.jpg |Other |2 |
+---------+--------------+---------+---------+
|4 |35698.jpg |PI |1 |
+---------+--------------+---------+---------+
第三个"注释"表包含所有注释。
+---------+--------------+---------+
|Id |Comment |User |
+---------+--------------+---------+
|1 |blah blah... |1 |
+---------+--------------+---------+
|2 |blah blah... |1 |
+---------+--------------+---------+
|3 |blah blah... |2 |
+---------+--------------+---------+
我想做的是编写一个返回的查询
Users.Id, Users.UserName, Images.Image (最大 Id & Type==PI), Comments.Id, Comments.Comment
基本上,这一切都是关于连接三张桌子。
这是我的代码
SELECT U.`Id`, U.`UserName`, I.`Image`, C.`Id`, C.`Comment`
FROM `Users` U
INNER JOIN `Images` I ON I.`Image` = (
SELECT MAX(I.`Image`) FROM `Images` WHERE I.`User` = U.`Id`
) AND I.`Type` = 'PI'
INNER JOIN `Comments` C ON C.`User` = U.`Id`
但这会返回图像表中的所有图像。例如,它返回用户 1 的两个图像。我只需要上次上传的图像。
您需要对结果进行降序排序,以使最后一个结果位于顶部,然后将结果限制为 1。这样的事情就可以了:
SELECT U.`Id`, U.`UserName`, I.`Image`, C.`Id`, C.`Comment`
FROM `Users` U
INNER JOIN `Images` I ON I.`User`=U.`Id`
INNER JOIN Comments c ON C.`User` = U.`Id`
WHERE I.`Id` IN ( SELECT MAX(`Id`) FROM `Images` GROUP BY `User` )
ORDER BY I.`Image` DESC
LIMIT 1
我认为这是您要针对的查询:
SELECT U.`Id`, U.`UserName`, I.`Image`, C.`Id`, C.`Comment`
FROM `Users` U INNER JOIN
`Images` I
ON I.id = (SELECT MAX(I.id)
FROM `Images`
WHERE `User` = U.`Id` and `Type` = 'PI'
) INNER JOIN
`Comments` C
ON C.`User` = U.`Id` ;
我确实发现这是一种编写查询的奇怪方式。 我倾向于在单独的步骤中进行max()
比较:
SELECT U.`Id`, U.`UserName`, I.`Image`, C.`Id`, C.`Comment`
FROM `Users` U INNER JOIN
`Images` I
ON i.`User` = U.`Id` INNER JOIN
(select user, max(I.id) as maxid
FROM `Images`
WHERE `Type` = 'PI'
) maxi
ON i.id = maxi.maxid join
`Comments` C
ON C.`User` = U.`Id` ;
这将为您提供所需的结果。在子查询上具有LEFT JOIN
的目的是为每个用户名提供一条记录,即使它在表 Images
上没有匹配的记录。LEFT JOIN
上的子查询还有一个子查询,该子查询获取每个user
的最新更新image
。
SELECT a.Id,
a.UserName,
c.Image,
b.Id,
b.Comment
FROM User a
INNER JOIN Comments b
ON a.ID = b.User
LEFT JOIN
(
SELECT i.*
FROM Images i
INNER JOIN
(
SELECT User, MAX(ID) ID
FROM Images
WHERE Type = 'PI'
GROUP BY User
) o ON i.User = o.User
AND i.ID = o.ID
) c ON a.ID = c.User
- SQLFiddle 演示
否则,如果您确定表 Images
上至少有一个匹配的记录,则可以在所有连接上使用 INNER JOIN
,
SELECT a.Id,
a.UserName,
c.Image,
b.Id,
b.Comment
FROM User a
INNER JOIN Comments b
ON a.ID = b.User
INNER JOIN Images c
ON a.ID = c.User
INNER JOIN
(
SELECT User, MAX(ID) ID
FROM Images
WHERE Type = 'PI'
GROUP BY User
) o ON i.User = o.User
AND i.ID = o.ID
- SQLFiddle 演示