代码之家 › 专栏 › 技术社区 › lumberjack4

组合SQL行

sql-server-ce sql-server sql

lumberjack4 · 技术社区 · 16 年前

我有一个包含IP数据包头表的SQL Compact数据库。表格如下:

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专家,但对此的任何帮助都将不胜感激。

2 回复 | 直到 16 年前

Mark Byers 16 年前

试试这个:

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, SrcPort, DestAddress, DestPort) AS T1
LEFT JOIN (
    SELECT SrcAddress, SrcPort, DestAddress, DestPort, SUM(Bytes) AS Bytes
    FROM PacketHeaders
    GROUP BY SrcAddress, SrcPort, DestAddress, 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, SrcPort, DestAddress, DestPort, Bytes) VALUES
(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),
(7, '10.0.25.2', 255, '10.0.25.50', 500, 48),
(8, '10.0.25.52', 255, '10.0.25.50', 500, 48);

结果如下:

'10.0.25.1', 255, '10.0.25.50', 500, 208, 112, 96
'10.0.25.2', 255, '10.0.25.50', 500, 48, 48, 0
'10.0.25.52', 255, '10.0.25.50', 500, 48, 48, 0
'74.26.9.40', 198, '75.48.0.25', 387, 136, 64, 72

它的工作方式是首先对单向对话进行分组,并计算字节总数。这就保证了每一个对话都将被精确地表示两次——每个方向一次。然后,此结果将自联接以提供所需的结果,通过强制a的(地址、端口)必须小于b来筛选重复项。使用左联接允许单向对话。

MatBailie 16 年前

我可以看到两种基本的方法…
1.全部分组,忽略a->b和b->a,然后自行加入结果。
2。在“SRC”字段中用“最低”IP地址重新排列数据,同时创建一个“方向”字段。

选项2可能是我的方式…

SELECT
    SrcAddress,
    SrcPort,
    DestAddress,
    DestPort,
    SUM(AtoB) + SUM(BtoA),
    SUM(AtoB),
    SUM(BtoA)
FROM
(
    SELECT
       CASE WHEN SrcAddress < DestAddress THEN SrcAddress  ELSE DestAddress END AS SrcAddress,
       CASE WHEN SrcAddress < DestAddress THEN SrcPort     ELSE DestPort    END AS SrcPort,
       CASE WHEN SrcAddress < DestAddress THEN DestAddress ELSE SrcAddress  END AS DestAddress,
       CASE WHEN SrcAddress < DestAddress THEN DestPort    ELSE ScrPort     END AS DestPort,
       CASE WHEN SrcAddress < DestAddress THEN Bytes       ELSE 0           END AS AtoB,
       CASE WHEN SrcAddress < DestAddress THEN 0           ELSE Bytes       END AS BtoA
    FROM
      PacketHeaders
)
    AS [data]
GROUP BY
    SrcAddress,
    SrcPort,
    DestAddress,
    DestPort

编辑

其他一些答案有我称之为选项1的版本。我也会尝试一下,而不是对人们的答案发表评论:(

SELECT
   ISNULL([AtoB].SrcAddress,  [BtoA].DestAddress)
   ISNULL([AtoB].SrcPort,     [BtoA].DestPort)
   ISNULL([AtoB].DestAddress, [BtoA].SrcAddress)
   ISNULL([AtoB].DestPort,    [BtoA].SrcPort)
   ISNULL([AtoB].Bytes,0) + ISNULL([BtoA].Bytes,0),
   ISNULL([AtoB].Bytes,0),
   ISNULL([BtoA].Bytes,0)
FROM
   (
      SELECT   SrcAddress, SrcPort, DestAddress, DestPort, SUM(Bytes) AS Bytes
      FROM     PacketHeaders
      WHERE    SrcAddress <= DestAddress
      GROUP BY SrcAddress, SrcPort, DestAddress, DestPort
   )
   AS [AtoB]
FULL OUTER JOIN
   (
      SELECT   SrcAddress, SrcPort, DestAddress, DestPort, SUM(Bytes) AS Bytes
      FROM     PacketHeaders
      WHERE    SrcAddress > DestAddress
      GROUP BY SrcAddress, SrcPort, DestAddress, DestPort
   )
   AS [BtoA]
      ON  [AtoB].SrcAddress  = [BtoA].DestPort
      AND [AtoB].SrcPort     = [BtoA].DestAddress
      AND [AtoB].DestAddress = [BtoA].SrcPort
      AND [AtoB].DestPort    = [BtoA].SrcAddress

但我说过我不会那样做…