来自子查询的 MySQL GROUP_CONCAT [英] MySQL GROUP_CONCAT from subquery

问题描述

我正在尝试从外部查询中的每一行获取一个 concat 列表，但我在其他地方得到 Unknown column 'outter_client' in 'where 子句' 我看到您无法访问外部来自子查询的变量，有没有办法在没有存储过程、视图或临时表的情况下重新制定此查询并使其正常工作?

I'm trying to get a concat list from every row in an outer query but I get Unknown column 'outter_client' in 'where clause' elsewhere I saw that you can't access an outer variable from a subquery, is there a way to reformulate this query without stored procedures, views or temporary tables and get it working?

SELECT client AS outer_client, top_domain
FROM log
JOIN (
   SELECT GROUP_CONCAT(t.domain) AS top_domain 
   FROM (
      SELECT domain 
      FROM log
      WHERE client = outer_client 
      GROUP BY 1 
      ORDER BY SUM(bytes)
      LIMIT 5
   ) t
) k
GROUP BY client
ORDER BY SUM(bytes)
LIMIT 5;

推荐答案

我认为您希望每个客户端的前五个域都基于字节数.您可以使用嵌套选择和 substring_index()/group_concat() 技巧来做到这一点:

I think you want the top five domains for each client, based on the number of bytes. You can do this using nested selects and the substring_index()/group_concat() trick:

select client,
       substring_index(group_concat(domain order by sumbytes desc), ',', 5) as top5domains
from (select client, domain, sum(bytes) as sumbytes
      from log
      group by client, domain
     ) cd
group by client
order by sum(sumbytes) desc
limit 5;

这篇关于来自子查询的 MySQL GROUP_CONCAT的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！