可以使用自联接：

SELECT a.cola,
       a.colb,
       ABS(a.dist - b.dist) AS dist_difference
FROM   table1 a
       INNER JOIN table1 b
       ON (a.cola = b.colb AND a.colb = b.cola)

其中，对于样本数据：

CREATE TABLE table1 (cola, colb, dist) AS
SELECT 1, 2,  50 FROM DUAL UNION ALL
SELECT 2, 1, 100 FROM DUAL UNION ALL
SELECT 3, 4, 100 FROM DUAL UNION ALL
SELECT 4, 3, 200 FROM DUAL;

输出：
| COLB|距离差异| DIST_DIFFERENCE |
| - -----|- -----| ------------ |
| 一个|五十| 50 |
| 2|五十| 50 |
| 三个|一百| 100 |
| 4|一百| 100 |
如果你只想为每一对使用一个值，那么你可以使用GREATEST和LEAST进行聚合：

SELECT LEAST(cola, colb) AS cola,
       GREATEST(cola, colb) AS colb,
       MAX(dist) - MIN(dist) AS dist_difference
FROM   table1
GROUP BY
       LEAST(cola, colb),
       GREATEST(cola, colb)

其输出：
| COLB|距离差异| DIST_DIFFERENCE |
| - -----|- -----| ------------ |
| 2|五十| 50 |
| 4|一百| 100 |
fiddle

计算值之间的差值 并创建具有此差异的附加列，则可以在SQL中使用JOIN子句和ABS（绝对值）函数。下面是完成此任务的示例查询：

SELECT
    a.Cola,
    a.ColB,
    ABS(a.dist - b.dist) AS Diferenca_dist
FROM
    table1 a
    JOIN table1 b ON a.Cola = b.ColB AND a.ColB = b.Cola
WHERE
    a.Cola <> a.ColB;

查询说明：
该查询从表table1中选择列Cola和ColB，并计算这两个值之间的绝对差值 从表A和B的列dist的。
JOIN子句用于合并table1表中的行，其中a.Cola的值对应于b.ColB的值，a.ColB的值对应于b.Cola的值。
WHERE子句将筛选出.Cola与.colB不同的行，确保只获取列之间存在差异的记录。
查询结果将显示Diferenca_dist列，其中包含dist列值之间的差值 对于Cola和ColB列中的相应记录。

其中一个选项是使用MODEL clause。当你需要多个行/列计算时，它非常方便。

WITH      -- S a m p l e   D a t a    1:
    tbl (COLA, COLB, DIST) AS
        ( Select 1, 2,  50  From Dual Union All
          Select 2, 1, 100  From Dual Union All
          Select 3, 4, 100  From Dual Union All
          Select 4, 3, 200  From Dual 
        )

然而，这个问题有点不清楚。这些行是成对的，我不确定是否希望第一个成对的行保留它的DIST值，并仅在第二个成对的行中计算差值。如果是这样的话，你可以像这样试试：

Select   COLA, COLB, Case When DIST = DIST_2 Then DIST Else DIST - DIST_2 End "DIFF"
From    ( Select ROWNUM "RN", COLA, COLB, DIST, DIST "DIST_2"
          From tbl  
          Order By COLA
        ) MODEL   
            Dimension By (RN, COLA, COLB)
            Measures(DIST, DIST_2)
            RULES ( DIST_2[ANY, ANY, ANY] = Nvl(DIST[CV(RN)-1, CV(COLB), CV(COLA)], DIST[CV(), CV(), CV()]) )
Order By RN
--  R e s u l t :
--        COLA       COLB       DIFF
--  ---------- ---------- ----------
--           1          2         50
--           2          1         50
--           3          4        100
--           4          3        100

另一方面，如果您希望在两个配对行中计算相同的差异，请尝试以下代码：

--   M a i n   S Q L    2:
Select   COLA, COLB, ABS(DIST_2) "DIFF"
From    ( Select ROWNUM "RN", COLA, COLB, DIST, DIST "DIST_2"
          From tbl  
          Order By COLA
        ) MODEL   
            Dimension By (RN, COLA, COLB)
            Measures(DIST, DIST_2)
            RULES ( DIST_2[ANY, ANY, ANY] = Nvl(DIST[CV(RN)+1, CV(COLB), CV(COLA)], DIST[CV(RN)-1, CV(COLB), CV(COLA)]) - DIST[CV(), CV(), CV()])
Order By RN

提供的示例数据的结果是相同的，但如果将最后一行中的DIST值从200更改为199，则会看到差异。

WITH      -- S a m p l e   D a t a     2:
    tbl (COLA, COLB, DIST) AS
        ( Select 1, 2,  50  From Dual Union All
          Select 2, 1, 100  From Dual Union All
          Select 3, 4, 100  From Dual Union All
          Select 4, 3, 199  From Dual 
        )

--  R e s u l t  (for 1st code):
--        COLA       COLB       DIFF
--  ---------- ---------- ----------
--           1          2         50
--           2          1         50
--           3          4        100
--           4          3         99

--  R e s u l t  (for 2nd code):
--        COLA       COLB       DIFF
--  ---------- ---------- ----------
--           1          2         50
--           2          1         50
--           3          4         99
--           4          3         99