我有sql压缩数据库,其中包含一个IP数据包头表.桌子看起来像这样：
Table: PacketHeaders ID SrcAddress SrcPort DestAddress DestPort Bytes 1 10.0.25.1 255 10.0.25.50 500 64 2 10.0.25.50 500 10.0.25.1 255 80 3 10.0.25.50 500 10.0.25.1 255 16 4 75.48.0.25 387 74.26.9.40 198 72 5 74.26.9.40 198 75.48.0.25 387 64 6 10.0.25.1 255 10.0.25.50 500 48
我需要执行一个查询来显示通过本地网络进行的“对话”.来自A的数据包 – > B是与B – >的数据包相同的会话的一部分. A.我需要执行一个查询来显示正在进行的对话.基本上我需要的是这样的东西：
Returned Query: SrcAddress SrcPort DestAddress DestPort TotalBytes BytesA->B BytesB->A 10.0.25.1 255 10.0.25.50 500 208 112 96 75.48.0.25 387 74.26.9.40 198 136 72 64
如您所见,我需要查询(或一系列查询)来识别A-> B与B-> A相同,并相应地分解字节计数.我不是任何手段的sql大师,但任何帮助,将不胜感激.
解决方法
尝试这个：
SELECT
T1.SrcAddress,T1.SrcPort,T1.DestAddress,T1.DestPort,T1.Bytes + COALESCE(T2.Bytes,0) AS TotalBytes,T1.Bytes AS A_to_B,COALESCE(T2.Bytes,0) AS B_to_A
FROM (
SELECT SrcAddress,SrcPort,DestAddress,DestPort,SUM(Bytes) AS Bytes
FROM PacketHeaders
GROUP BY SrcAddress,DestPort) AS T1
LEFT JOIN (
SELECT SrcAddress,DestPort) AS T2
ON T1.SrcAddress = T2.DestAddress
AND T1.SrcPort = T2.DestPort
AND T1.DestAddress = T2.SrcAddress
AND T1.DestPort = T2.SrcPort
WHERE T1.SrcAddress < T1.DestAddress OR
(T1.SrcAddress = T1.DestAddress AND T1.SrcPort = T1.DestPort) OR
T2.DestAddress IS NULL
关于这个测试数据：
CREATE TABLE PacketHeaders (ID INT,SrcAddress NVARCHAR(100),SrcPort INT,DestAddress NVARCHAR(100),DestPort INT,Bytes INT); INSERT INTO PacketHeaders (ID,SrcAddress,Bytes) VALUES (1,'10.0.25.1',255,'10.0.25.50',500,64),(2,80),(3,16),(4,'75.48.0.25',387,'74.26.9.40',198,72),(5,(6,48),(7,'10.0.25.2',(8,'10.0.25.52',48);
这给出以下结果：
'10.0.25.1',208,112,96 '10.0.25.2',48,0 '10.0.25.52',0 '74.26.9.40',136,64,72
它的工作方式是首先组合单向会话并总计字节数.这样可以确保每个会话都会被正确地表示两次 – 每次方向一次.然后,此结果将自动连接以提供所需的结果,通过强制A(地址,端口)必须小于B来过滤重复项.使用左连接可以进行单向对话.
