如何使用join语句按desc排序？

mm5n2pyu 于 2021-06-19 发布在 Mysql

关注(0)|答案(2)|浏览(403)

我有这样一个问题：

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 在 ORDER BY clicks.clickDate DESC . 但它给了我一个错误：
order by子句的表达式#1不在group by子句中，并且包含未聚合的列
所以我有两个问题：
有没有可能 ORDER BY 如果我使用 GROUP_CONCAT 单击。单击日期列？也许有一种方法可以只显示最后一个值 GROUP_CONCAT 点击。点击日期？

sql mysql sql-order-by

来源：https://stackoverflow.com/questions/53158389/how-to-order-by-desc-with-join-statement

2条答案

按热度按时间

rqqzpn5f1#

你可以尝试添加 order by 在 GROUP_CONCAT 并添加一列 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

赞(0）回复(0）举报 2021-06-20

lymnna712#

尝试按每条记录中最大的单击日期排序。注意，我们需要将文本日期数值转换为实际整数，这样排序才能正常工作。

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

赞(0）回复(0）举报 2021-06-20

我来回答

如何使用join语句按desc排序？

2条答案

相关问题

热门标签

最新问答