php - Mysql GROUP BY和ORDER BY DESC

This question already has answers here:

Get top n records for each group of grouped results

(11个答案)


7年前关闭。




这是我的完整查询:

SELECT * FROM `clientgroupassign`
LEFT JOIN `clients` ON `clientgroupassign`.clientId = `clients`.clientId
LEFT JOIN `users` ON `carerId` = `userId`
LEFT JOIN
    (SELECT * FROM
        (SELECT * FROM `contacts` WHERE `contactGroup` = 4 ORDER BY `contactId` DESC)
        as `contacts`
    GROUP BY (`contactClientId`)
    )  AS `contacts` ON `contactClientId` = `clients`.clientId
WHERE groupId = 4
ORDER BY `clients`.clientId

第三次连接存在问题，导致脚本执行约1分钟。当我在PMA中分别运行它时:

SELECT * FROM (SELECT * FROM `contacts` WHERE `contactGroup` = 4 ORDER BY `contactId` DESC) AS `contacts` GROUP BY (`contactClientId`)

它仍然需要很长时间才能执行。

我想要的是从contacts中为第4组中的每个客户端获取最后一个添加的行(客户端可以在各个组中)。

谢谢。

最佳答案

要获取“来自第4组中每个客户的联系人的最后添加的行”，请尝试以下操作:

SELECT
    c.*
FROM(
    SELECT
        contactClientId,
        MAX(contactId) as cid
    FROM
        contacts
    WHERE
        contactGroup = 4
    GROUP BY
        contactClientId
    ORDER BY
        NULL
) as tmp
INNER JOIN contacts as c
    ON c.contactId = tmp.cid
    AND c.contactClientId = tmp.contactClientId

如果contactId在contacts中为PK，则不需要第二个join子句。



完整的docs。

完整查询:

SELECT * FROM `clientgroupassign`
LEFT JOIN `clients` ON `clientgroupassign`.clientId = `clients`.clientId
LEFT JOIN `users` ON `carerId` = `userId`
LEFT JOIN
    (
        SELECT
            c.*
        FROM(
            SELECT
                contactClientId,
                MAX(contactId) as cid
            FROM
                contacts
            WHERE
                contactGroup = 4
            GROUP BY
                contactClientId
            ORDER BY
                NULL
        ) as tmp
        INNER JOIN contacts as c
            ON c.contactId = tmp.cid
            AND c.contactClientId = tmp.contactClientId
    )  AS `contacts` ON `contactClientId` = `clients`.clientId
WHERE groupId = 4
ORDER BY `clients`.clientId

关于php - Mysql GROUP BY和ORDER BY DESC ，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/18831155/