我已经写了这个代码来从我的数据库中获取一些信息。
"SELECT
c.from AS user_id,
c.time AS time,
u.user_firstname AS user_firstname,
u.user_lastname AS user_lastname,
u.user_profile_picture AS user_profile_picture
FROM chat c INNER JOIN users u ON u.user_id = c.from WHERE c.to = :id1
UNION SELECT
c.to AS user_id,
c.time AS time,
u2.user_firstname AS user_firstname,
u2.user_lastname AS user_lastname,
u2.user_profile_picture AS user_profile_picture
FROM chat c INNER JOIN users u2 ON u2.user_id = c.to WHERE c.from= :id2
ORDER BY time DESC"
一切都很好，除了一件事。由于这是一个收件箱脚本，因此有来自同一用户的许多消息。但是我只需要检查是否有来自这个用户的消息。
array (size=28)
0 =>
array (size=5)
'user_id' => int 6
'time' => string '2014-05-13 19:53:58' (length=19)
'user_firstname' => string 'john' (length=4)
'user_lastname' => string 'doe' (length=3)
'user_profile_picture' => string '6_user_profile.jpg' (length=19)
1 =>
array (size=5)
'user_id' => int 2
'time' => string '2014-05-13 16:59:50' (length=19)
'user_firstname' => string 'james' (length=5)
'user_lastname' => string 'bond' (length=4)
'user_profile_picture' => string '2_user_profile.jpg' (length=19)
2 =>
array (size=5)
'user_id' => int 6
'time' => string '2014-05-13 02:15:44' (length=19)
'user_firstname' => string 'john' (length=4)
'user_lastname' => string 'doe' (length=3)
'user_profile_picture' => string '6_user_profile.jpg' (length=19)
3 =>
array (size=5)
'user_id' => int 6
'time' => string '2014-05-13 02:13:21' (length=19)
'user_firstname' => string 'john' (length=4)
'user_lastname' => string 'doe' (length=3)
'user_profile_picture' => string '6_user_profile.jpg'(length=19)
4 =>
array (size=5)
'user_id' => int 2
'time' => string '2014-05-13 01:58:59' (length=19)
'user_firstname' => string 'james' (length=5)
'user_lastname' => string 'bond' (length=4)
'user_profile_picture' => string '2_user_profile.jpg'(length=19)
有3个John doe和2个James bond。但我只需要最后一批，根据时间。所以在这个例子中，约翰·多伊来自19:53:58，詹姆斯·邦德来自16:59:50。这样的:
array (size=2)
0 =>
array (size=5)
'user_id' => int 6
'time' => string '2014-05-13 19:53:58' (length=19)
'user_firstname' => string 'john' (length=4)
'user_lastname' => string 'doe' (length=3)
'user_profile_picture' => string '6_user_profile.jpg' (length=19)
1 =>
array (size=5)
'user_id' => int 2
'time' => string '2014-05-13 16:59:50' (length=19)
'user_firstname' => string 'james' (length=5)
'user_lastname' => string 'bond' (length=4)
'user_profile_picture' => string '2_user_profile.jpg' (length=19)
，如果有其他用户，我也想获得他们的最后记录。我该怎么做呢?只有一个查询是可能的吗?
试试我测试过的这个
"SELECT *
FROM (SELECT
c.from AS user_id,
c.time AS time,
u.user_firstname AS user_firstname,
u.user_lastname AS user_lastname,
u.user_profile_picture AS user_profile_picture
FROM chat c INNER JOIN users u ON u.user_id = c.from WHERE c.to = :id1
UNION
SELECT
c.to AS user_id,
c.time AS time,
u2.user_firstname AS user_firstname,
u2.user_lastname AS user_lastname,
u2.user_profile_picture AS user_profile_picture
FROM chat c INNER JOIN users u2 ON u2.user_id = c.to WHERE c.from= :id2
) AS bynames
GROUP BY user_id ORDER BY time ASC"
将联合放在子查询中，在主查询中排序，并将其限制为最近的行
SELECT *
FROM (SELECT
c.from AS user_id,
c.time AS time,
u.user_firstname AS user_firstname,
u.user_lastname AS user_lastname,
u.user_profile_picture AS user_profile_picture
FROM chat c INNER JOIN users u ON u.user_id = c.from WHERE c.to = :id1
UNION
SELECT
c.to AS user_id,
c.time AS time,
u2.user_firstname AS user_firstname,
u2.user_lastname AS user_lastname,
u2.user_profile_picture AS user_profile_picture
FROM chat c INNER JOIN users u2 ON u2.user_id = c.to WHERE c.from= :id2
)
ORDER BY time DESC
LIMIT 1
未经测试
SELECT c.user_id
, MAX(c.time)
, u.user_firstname
, u.user_lastname
, u.user_profile_picture
FROM (
SELECT from AS user_id
, time
FROM chat
WHERE to = :id1
UNION
SELECT to AS user_id
, time
FROM chat
WHERE from = :id2
) as c
JOIN users u
ON u.user_id = c.user_id
GROUP BY c.user_id
, u.user_firstname
, u.user_lastname
, u.user_profile_picture