单表中成对反向记录的列值对比及Dist差值计算技术问询

原始数据表 #

需求说明 #

在单表中找出满足一条记录的Col A等于另一条记录的Col B，且该记录的Col B等于另一条记录的Col A的成对记录，计算这两条记录Dist字段的差值（取绝对值），生成如下期望结果表：

期望结果表 #

已尝试的SQL（仅筛选记录，无法计算差值） #

SELECT
    a.*
FROM
    table1 a
WHERE
    EXISTS (
        SELECT
            1
        FROM
            table1 b
        WHERE
            a.Col A = b.Col B and a.Col B= b.Col A
    )
and Col A  <> Col B

解决方案 #

通过自连接关联成对记录，直接计算Dist字段的绝对值差值，SQL语句如下：

SELECT
    a.`Col A`,
    a.`Col B`,
    ABS(a.Dist - b.Dist) AS `Dist Difference`
FROM
    table1 a
JOIN
    table1 b ON a.`Col A` = b.`Col B` AND a.`Col B` = b.`Col A`
WHERE
    a.`Col A` <> a.`Col B`;

关键说明 #

• 用JOIN替代EXISTS，可以同时获取当前记录和其配对记录的Dist值，直接进行差值计算
• ABS()函数确保差值为非负绝对值，匹配需求中的结果
• 若数据库要求，带空格的列名需用反引号（如MySQL）或双引号（如PostgreSQL）包裹，避免语法错误

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