mysql - 如何使用 JOIN 语句进行 ORDER BY DESC？

Question

我有这样的查询:

SELECT
        result.ip,
        ips.ipStatus,
        result.quantity,
        result.clickDates,
    FROM
        ips
    INNER JOIN
        (
            SELECT
                visits.ip,
                count(visits.ip) AS quantity,
                GROUP_CONCAT(clicks.clickDate) AS clickDates,
            FROM
                visits
            INNER JOIN
                clicks 
                    ON visits.id = clicks.id 
            WHERE
                clicks.clickDate BETWEEN 1 AND 10 
            GROUP BY
                visits.ip 
            ORDER BY
                null
        ) AS result 
            ON ips.ip = result.ip LIMIT 6, 2

它产生这样的结果表:

   ip    |    status    |    quantity    |       date 

1.1.1.1        ok               3            555, 557, 558
2.2.2.2        ok               1            657

我的日期存储在 .ms (BIGINT) 中。我的目标是 ORDER BY 日期 DESC。我想最新的 ips 将在顶部。因此，我试图在 ORDER BY clicks.clickDate DESC 上更改 ORDER BY。但它给了我一个错误:

Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column

所以我有两个问题:

如果我在 clicks.clickDate 列上使用 GROUP_CONCAT，是否可以ORDER BY？也许有一种方法可以在 clicks.clickDate 上仅显示 GROUP_CONCAT 之后的最后一个值？

Answer 1

可以尝试在GROUP_CONCAT中添加order by，添加一列MAX(clicks.clickDate)得到clickDate 组中的最大值，用于主查询 order by。

SELECT
    result.ip,
    ips.ipStatus,
    result.quantity,
    result.clickDates,
FROM
    ips
INNER JOIN
    (
        SELECT
            visits.ip,
            count(visits.ip) AS quantity,
            GROUP_CONCAT(clicks.clickDate ORDER BY clicks.clickDate desc) AS clickDates,
            MAX(clicks.clickDate) maxDt
        FROM
            visits
        INNER JOIN
            clicks 
                ON visits.id = clicks.id 
        WHERE
            clicks.clickDate BETWEEN 1 AND 10 
        GROUP BY
            visits.ip 
    ) AS result 
        ON ips.ip = result.ip 
ORDER BY maxDt DESC
LIMIT 6, 2