MySQL连接到同一个表中的列

Question

我有一张IP流量数据表

SRC_IP | SRC_BW |夏令时IP |夏令时BW

对于我关心的大多数 IP 地址，它们会在两列中一次或多次出现，但有些只会作为 SRC 或 DST 出现。我正在尝试获取唯一 IP 的列表并总结它们的带宽(无论它是连接的来源还是目的地)。

我目前有:

SELECT SRC_IP, SRC_BYTES, DST_BYTES, SRC_BYTES + IFNULL(DST_BYTES,0) as TOTAL_BYTES
FROM
    (SELECT INET6_NTOA(bro_conn.CONN_ORIGH) as SRC_IP, 
            SUM(bro_conn.CONN_ORIGIPBYTES) AS SRC_BYTES 
     FROM bro_conn GROUP BY SRC_IP) src
LEFT JOIN 
    (SELECT INET6_NTOA(bro_conn.CONN_RESPH) as DST_IP, 
            SUM(bro_conn.CONN_RESPIPBYTES) AS DST_BYTES 
     FROM bro_conn GROUP BY DST_IP) dst
ON src.SRC_IP = dst.DST_IP

我的问题是，如果一个地址只显示为 DST_IP，它就不会出现在列表中......有什么想法吗？

Answer 1

您可以将UNION ALL 与条件聚合 一起使用，并摆脱LEFT JOIN 操作:

SELECT IP AS SRC_IP,
       SUM(CASE WHEN Type = 'SRC' THEN BYTES ELSE 0 END) AS SRC_BYTES,
       SUM(CASE WHEN Type = 'DST' THEN BYTES ELSE 0 END) AS DST_BYTES,
       SUM(BYTES) AS TOTAL_BYTES
FROM (
  SELECT INET6_NTOA(bro_conn.CONN_ORIGH) as IP, 
         SUM(bro_conn.CONN_ORIGIPBYTES) AS BYTES,
         'SRC' AS Type 
  FROM bro_conn 
  GROUP BY SRC_IP

  UNION ALL

  SELECT INET6_NTOA(bro_conn.CONN_RESPH) as IP, 
         SUM(bro_conn.CONN_RESPIPBYTES) AS BYTES,
         'DST' AS Type  
  FROM bro_conn GROUP BY DST_IP) AS t
GROUP BY IP