php - 通过不给出真实结果来排序

Question

你好，我试过这个查询:

SELECT 
    * 
FROM 
    (
        SELECT 
            * 
        FROM 
            specials_companies_mails 
        WHERE 
            `toId` = 1 && parentId <> 0 
        ORDER BY 
            specials_companies_mails.mailId DESC
    ) AS b 
GROUP BY 
    b.parentId

我在表中有两行:

mailID           subject        toId        parentId
3                test1          1           2
4                test2          1           2

以及查询后的结果:

mailID           subject        toId        parentId
3                test1          1           2

它的意思是

ORDER BY specials_companies_mails.mailId DESC

不工作...它给我 mailId 3 而不是 4...

怎么办？

非常感谢

Answer 1

GROUP BY 不关心 ORDER BY。所选记录将完全不确定。

你可以做的是为每个 parentId 找到最新的 mainId，如下所示:

select a.* 
from specials_companies_mails AS a
left join specials_companies_mails AS b
    on  a.parentId = b.parentId
    and a.mailID < b.mailID
    and b.toId = 1 
    and b.parentId <> 0
WHERE b.parentId is null
    and a.toId = 1 
    and a.parentId <> 0