我正在编写一个消息传递系统,我正在尝试选择按升序发送的最后 10 条消息(底部为最新消息)。
但是,这样做有问题:(
这是我当前的 SELECT 语句,表中有 30 多行可用。
SELECT * FROM
( SELECT * FROM messages ORDER BY addedDate DESC LIMIT 10 ) tb
WHERE ( senderID = "1" OR receiverID = "1" )
AND ( senderID = "3" OR receiverID = "3" )
ORDER BY addedDate ASC LIMIT 10
但是,由于某种原因,这只返回 7 行,当使用发送方和接收方的不同组合时,如果行数少于 7 个结果,即使每个行至少应该给我我想要的 10 行。
有没有另一种方法可以用来获取匹配的底部 10 行:
WHERE ( senderID = "1" OR receiverID = "1" )
AND ( senderID = "3" OR receiverID = "3" )
但按升序选择底部 10 个。
子查询中应该有 WHERE 语句,而不是主查询。
试试这个:
SELECT * FROM
( SELECT * FROM messages
WHERE ( senderID = "1" OR receiverID = "1" )
AND ( senderID = "3" OR receiverID = "3" )
ORDER BY addedDate DESC LIMIT 10 ) tb
ORDER BY addedDate ASC
而且你不需要第二个 LIMIT,因为你只能从子查询中获得 10 个结果。