mysql - 使用子查询重写查询

Question

考虑一个包含 Id、Name、Surname 列的用户表和包含 Ip 和 Actor 列的操作表。我需要为每个 Ip 检索使用该 Ip 执行操作的用户集。我现在拥有的看起来像:

SELECT a.ip, (
    SELECT GROUP_CONCAT(t.id, '-', t.name, ' ', t.surname) FROM(
        SELECT ud.id, ud.name, ud.surname
    FROM users_data AS ud
    JOIN actions AS a2 ON a2.actor = ud.id
    WHERE a2.ip = a.ip
    GROUP BY ud.id) AS t
    )
    FROM actions AS a
WHERE a.ip != '' AND a.ip != '0.0.0.0'
GROUP BY a.ip

它不起作用，因为内部子查询的 where 子句中 a.ip 未知。

为了性能问题，我需要避免使用 DISTINCT。

有什么建议吗？

Answer 1

您可以将查询重写为

SELECT n.ip, GROUP_CONCAT( DISTINCT n.your_user SEPARATOR ' -- ') `users` FROM
(  
    SELECT a.ip  AS ip, CONCAT(t.id, '-', t.name, ' ', t.surname) `your_user`
    FROM users_data AS ud
    JOIN actions AS a ON a.actor = ud.id         
) `new_table` n
WHERE n.ip != '' AND n.ip != '0.0.0.0'
GROUP BY n.ip

Note Be aware of that the result is truncated to the maximum length that is given by the group_concat_max_len system variable, which has a default value of 1024