如何使用 ORACLE SQL 对特定行值进行操作？

admin

如何使用 ORACLE SQL 对特定行值进行操作？

sql

如何使用 ORACLE SQL 对特定行值进行操作？

这是我的 SQL 语法。

SELECT (BASICPAY + SUDANG)
     FROM(
     SELECT T2.*, ROWNUM AS RN FROM (
          SELECT T.*, (BASICPAY + SUDANG)
          FROM TBLINSA T
         WHERE SUBSTR(T.SSN, 8, 1) = 1
          ORDER BY BASICPAY + SUDANG DESC
      )T2
     )T3
WHERE RN = 3
UNION
SELECT (BASICPAY + SUDANG)
     FROM(
     SELECT T2.*, ROWNUM AS RN FROM (
          SELECT T.*, (BASICPAY + SUDANG)
          FROM TBLINSA T
         WHERE SUBSTR(T.SSN, 8, 1) = 2
          ORDER BY BASICPAY + SUDANG DESC
      )T2
     )T3
WHERE RN = 9

结果是：

(BASICPAY+SUDANG)
2070000
2670000

这里我们需要在第 1 行找到 2070000 和 2670000 之间的差异。

我很好奇如何获得这种差异。

阅读 122

收藏

2021-06-07

共1个答案

admin

您可以在from子句中使用子查询：

SELECT S1.TOTAL - S2.TOTAL
FROM (SELECT (BASICPAY + SUDANG) as TOTAL
      FROM (SELECT T2.*, ROWNUM AS RN 
            FROM (SELECT T.*
                  FROM TBLINSA T
                  WHERE SUBSTR(T.SSN, 8, 1) = 1
                  ORDER BY BASICPAY + SUDANG DESC
                 )T 2
           ) T3
       WHERE RN = 3
      ) S1 CROSS JOIN
      (SELECT (BASICPAY + SUDANG) AS TOTAL
       FROM (SELECT T2.*, ROWNUM AS RN
             FROM (SELECT T.*
                   FROM TBLINSA T
                   WHERE SUBSTR(T.SSN, 8, 1) = 2
                   ORDER BY BASICPAY + SUDANG DESC
                  ) T2
            ) T3
       WHERE RN = 9
      ) S2

2021-06-07