如何使用 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 之间的差异。
我很好奇如何获得这种差异。
您可以在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