我正在尝试从外部查询的每一行获取concat列表,但在Unknown column 'outter_client' in 'where clause'
其他地方,我看到您无法从子查询访问外部变量,有没有一种方法可以在没有存储过程,视图或临时条件的情况下重新构造此查询表并使其正常工作?
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()
技巧来做到这一点:
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;
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句