如何聚合合并src_ip与dst_ip互为对方的SQL表记录？

原数据表 #

+--------------+----------------+----------------+---------------------+
| flow_number  | src_ip         | dst_ip         | date                |
+--------------+----------------+----------------+---------------------+
|           1  | 1.1.1.1        | 192.168.2.218  | 2022-11-01 16:00:10 |
|           10 | 192.168.2.218  | 1.1.1.1        | 2022-11-01 16:00:12 |

注：原表第一条记录的src_ip为1.1.1.1.1，推测是笔误，修正为1.1.1.1以匹配目标结果

需求说明 #

将src_ip与dst_ip互为对方的记录合并为单条，核心要求：

• 对合并后的记录求和flow_number
• date字段可任选（如最新/最早日期）
• src_ip与dst_ip的顺序不做要求

问题分析 #

你之前尝试用MD5(CONCAT(src_ip,dst_ip))和MD5(CONCAT(dst_ip,src_ip))生成哈希列，但这两个哈希值是不同的——因为两个IP的拼接顺序相反，生成的字符串完全不一样，自然无法将双向记录归到同一分组。

解决方案 #

核心思路是让双向IP对生成统一的分组标识：通过固定IP的排序规则（比如按字符串大小排序），让互为反向的IP对拥有完全相同的分组键，以此实现聚合。

方法1：使用LEAST和GREATEST函数（推荐） #

多数数据库（如MySQL、PostgreSQL）支持这两个函数，能快速获取两个IP中的较小值和较大值，保证分组键统一：

SELECT
    SUM(flow_number) AS flow_number,
    LEAST(src_ip, dst_ip) AS src_ip,
    GREATEST(src_ip, dst_ip) AS dst_ip,
    MAX(date) AS date  -- 这里选最新日期，也可换成MIN(date)取最早日期
FROM flows
GROUP BY LEAST(src_ip, dst_ip), GREATEST(src_ip, dst_ip);

方法2：使用CASE语句兼容更多数据库 #

如果你的数据库不支持LEAST/GREATEST，可以用CASE语句实现相同逻辑：

SELECT
    SUM(flow_number) AS flow_number,
    CASE WHEN src_ip < dst_ip THEN src_ip ELSE dst_ip END AS src_ip,
    CASE WHEN src_ip < dst_ip THEN dst_ip ELSE src_ip END AS dst_ip,
    MAX(date) AS date
FROM flows
GROUP BY
    CASE WHEN src_ip < dst_ip THEN src_ip ELSE dst_ip END,
    CASE WHEN src_ip < dst_ip THEN dst_ip ELSE src_ip END;

执行结果 #

上述SQL会生成符合需求的结果：

+-------------+----------------+----------------+---------------------+
| flow_number | src_ip         | dst_ip         | date                |
+-------------+----------------+----------------+---------------------+
|          11 | 1.1.1.1        | 192.168.2.218  | 2022-11-01 16:00:12 |

内容的提问来源于stack exchange，提问作者user2913139