sql - 如何在 SQL 的嵌套连接中使用 GroupBy

Question

以下 SQL 语句返回用户名、IP 地址、持续时间、流量和上次访问。

            SELECT username, SUBSTRING_INDEX( callingstationid, '=', 1 ) as IP,
                   SUM(acctsessiontime) as `duration`, count(username) as Count, 
                   (SUM(`acctinputoctets`)+SUM(`acctoutputoctets`))/1000/1000/1000 as GBytes, 
                   MAX(acctstoptime) as `last visit`
            FROM radacct
            GROUP BY username

我想将此与 Country 表一起加入，并按国家/地区分组，以便我知道每个国家/地区使用了多少流量以及该国家/地区的最后一次访问是什么时候。

我无法正确加入：

    SELECT  c.country, 
            round(GBytes, 2),  
            Count, 
            duration as `Total Time Spent`,
            `last visit`
            FROM (
                SELECT username, 
                       SUBSTRING_INDEX( callingstationid, '=', 1 ) as IP,        
                       SUM(acctsessiontime) as `duration`, count(username) as Count,                  
                       (SUM(`acctinputoctets`)+SUM(`acctoutputoctets`))/1000/1000/1000 as GBytes,
                       MAX(acctstoptime) as `last visit`
                FROM radacct
                GROUP BY username
            ) filtered_radacct
            JOIN u_cache_db.`global_ip` c ON c.ip = filtered_radacct.IP
            GROUP BY c.country
            order by GBytes DESC;

我知道这个数字不可能是正确的。

标签： sqlmariadb

Answer 1

您还需要顶级查询中所有字段的聚合函数，即

SELECT  c.country, 
        round(SUM(GBytes), 2),  
        SUM(Count), 
        SUM(duration) as `Total Time Spent`,
        MAX(`last visit`)