mysql - 使用 GROUP_CONCAT 返回一行

Question

我的查询有问题，我想将所有会计数据组合在一起并 GROUP_CONCAT 客户站点名称和站点 IP。

我使用了 GROUP_CONCAT(site_name) as site_name 和 GROUP_CONCAT(site_ip) as site_ip 但我得到的结果仍然不正确，我在 GROUP_CONCAT 中得到了重复的 IP专栏。

我需要每个客户的单行数据，其中包含属于他们的每个站点 IP 的统计流量 SUM/Grouped。

我的查询是:

SELECT 
SUM(upload_bytes) as upload_bytes, 
SUM(download_bytes) as download_bytes, 
SUM(upload_bytes + download_bytes) as totalbytes, 
package_id, 
username, 
userid, 
networkaccess, 
packagename, 
speedlimit, 
threshold, 
throttlelimit, 
extendeddata, 
datalimitamount, 
accountingdays, 
GROUP_CONCAT(site_name) as site_name, 
GROUP_CONCAT(site_ip) as site_ip 
FROM 
(
    (
        SELECT 
            ip_accounting.src_address as site_ip, 
            SUM(ip_accounting.bytes) AS upload_bytes, 
            0 as download_bytes, 
            clients.username, 
            clients.userid, 
            clients.networkaccess, 
            clients.extendeddata, 
            data_packages.package_id, 
            data_packages.packagename, 
            data_packages.speedlimit, 
            data_packages.threshold, 
            data_packages.throttlelimit, 
            data_packages.datalimitamount, 
            data_packages.accountingdays, 
            client_site_ip.site_name 
        FROM 
            ip_accounting 
            join client_site_ip on client_site_ip.site_ip = ip_accounting.src_address 
            JOIN clients ON client_site_ip.userid = clients.userid 
            join data_packages on data_packages.package_id = clients.datapackage 
        WHERE 
            dst_address NOT BETWEEN INET_NTOA('192.168.0.1') 
            AND INET_NTOA('192.168.255.254') 
            and timeanddate BETWEEN SUBDATE(
                CURRENT_TIMESTAMP(), 
                INTERVAL data_packages.accountingdays DAY
            ) 
            AND CURRENT_TIMESTAMP() 
        GROUP BY 
            src_address
    ) 
    UNION ALL 
        (
            SELECT 
                ip_accounting.dst_address as site_ip, 
                0 AS upload_bytes, 
                SUM(ip_accounting.bytes) as download_bytes, 
                clients.username, 
                clients.userid, 
                clients.networkaccess, 
                clients.extendeddata, 
                data_packages.package_id, 
                data_packages.packagename, 
                data_packages.speedlimit, 
                data_packages.threshold, 
                data_packages.throttlelimit, 
                data_packages.datalimitamount, 
                data_packages.accountingdays, 
                client_site_ip.site_name 
            FROM 
                ip_accounting 
                join client_site_ip on client_site_ip.site_ip = ip_accounting.dst_address 
                JOIN clients ON client_site_ip.userid = clients.userid 
                join data_packages on data_packages.package_id = clients.datapackage 
            WHERE 
                src_address NOT BETWEEN INET_NTOA('192.168.0.1') 
                AND INET_NTOA('192.168.255.254') 
                and timeanddate BETWEEN SUBDATE(
                    CURRENT_TIMESTAMP(), 
                    INTERVAL data_packages.accountingdays DAY
                ) 
                AND CURRENT_TIMESTAMP() 
            GROUP BY 
                dst_address
        )
) a 
GROUP BY 
site_ip 
ORDER BY 
INET_ATON(site_ip)

我目前的结果是这样的:

Answer 1

你应该按非聚合列分组(而不是你在聚合函数中使用的列)例如:

  SELECT 
  SUM(upload_bytes) as upload_bytes, 
  SUM(download_bytes) as download_bytes, 
  SUM(upload_bytes + download_bytes) as totalbytes, 
  package_id, 
  username, 
  userid, 
  networkaccess, 
  packagename, 
  speedlimit, 
  threshold, 
  throttlelimit, 
  extendeddata, 
  datalimitamount, 
  accountingdays, 
  GROUP_CONCAT(DISTINCT site_name) as site_name, 
  GROUP_CONCAT(DISTINCT site_ip) as site_ip 
  FROM 
  (
      (
          SELECT 
              ip_accounting.src_address as site_ip, 
              SUM(ip_accounting.bytes) AS upload_bytes, 
              0 as download_bytes, 
              clients.username, 
              clients.userid, 
              clients.networkaccess, 
              clients.extendeddata, 
              data_packages.package_id, 
              data_packages.packagename, 
              data_packages.speedlimit, 
              data_packages.threshold, 
              data_packages.throttlelimit, 
              data_packages.datalimitamount, 
              data_packages.accountingdays, 
              client_site_ip.site_name 
          FROM 
              ip_accounting 
              join client_site_ip on client_site_ip.site_ip = ip_accounting.src_address 
              JOIN clients ON client_site_ip.userid = clients.userid 
              join data_packages on data_packages.package_id = clients.datapackage 
          WHERE 
              dst_address NOT BETWEEN INET_NTOA('192.168.0.1') 
              AND INET_NTOA('192.168.255.254') 
              and timeanddate BETWEEN SUBDATE(
                  CURRENT_TIMESTAMP(), 
                  INTERVAL data_packages.accountingdays DAY
              ) 
              AND CURRENT_TIMESTAMP() 
          GROUP BY 
              src_address
      ) 
      UNION ALL 
          (
              SELECT 
                  ip_accounting.dst_address as site_ip, 
                  0 AS upload_bytes, 
                  SUM(ip_accounting.bytes) as download_bytes, 
                  clients.username, 
                  clients.userid, 
                  clients.networkaccess, 
                  clients.extendeddata, 
                  data_packages.package_id, 
                  data_packages.packagename, 
                  data_packages.speedlimit, 
                  data_packages.threshold, 
                  data_packages.throttlelimit, 
                  data_packages.datalimitamount, 
                  data_packages.accountingdays, 
                  client_site_ip.site_name 
              FROM 
                  ip_accounting 
                  join client_site_ip on client_site_ip.site_ip = ip_accounting.dst_address 
                  JOIN clients ON client_site_ip.userid = clients.userid 
                  join data_packages on data_packages.package_id = clients.datapackage 
              WHERE 
                  src_address NOT BETWEEN INET_NTOA('192.168.0.1') 
                  AND INET_NTOA('192.168.255.254') 
                  and timeanddate BETWEEN SUBDATE(
                      CURRENT_TIMESTAMP(), 
                      INTERVAL data_packages.accountingdays DAY
                  ) 
                  AND CURRENT_TIMESTAMP() 
              GROUP BY 
                  dst_address
          )
  ) a 
  GROUP BY 
      package_id, 
      username, 
      userid, 
      networkaccess, 
      packagename, 
      speedlimit, 
      threshold, 
      throttlelimit, 
      extendeddata, 
      datalimitamount, 
      accountingdays