查找表中两行之间的差异.甲骨文 [英] Finding difference between two rows in a table. Oracle

问题描述

我有一个包含30多个字段的表，我想获取不同的字段名称和值.

I have a table with 30+ fields and I wanted to get the field name and the value that are different.

例如在表X中，我们有

ID |  City      | State | Zip   | Segment_One | Segment_Two | ....
1  |            |  NY   | 14228 |   X71       |   5         |
2  |  JamesTown |  NY   | 14845 |   X72       |   5         |

查询应返回字段名称和数据的差异.

The query should return the difference of both field name and the data.

ID | City      | Zip      | Segment_One
1  |           | 14228    |  X71
2  | JamesTown | 14845    |  X72

有一个查询，我可以始终将行限制为两行....所以我想我总是会在两行之间进行比较，但是如何找到字段名称的不同并获取这些值字段?

There is a query I can use to always limit the row to two rows.... so I guess I will always compare between the two rows, but how do I find the difference of field name and get the value for those fields?

这是我尝试过的方法，但即使状态不同，它似乎也会返回状态.

This is what I tried but it seems it return the State even if it is different.

select * from 
(select a.* from X a where ROWNUM = 1 order by last_updt_date desc ) 
minus
select * from
(select b.* from X b where ROWNUM = 2 order by last_updt_date desc );

结果:

ID  |  City  |  State  | Zip   |  Segment_One |  Segment_Two |
1   |        |   NY    | 14228 |  X71         |     5        | ...

这将返回行中的所有字段，但我只需要差异字段和值.这不会返回第2行.如果我将减号取反，它将什么也不会返回.

This returns all fields in the row but I want only the differences field and values. This does not return for row 2. If I reverse the minus then it will return nothing.

推荐答案

您可以尝试使用PL/SQL块和动态CURSOR这样的方法.注意，我在这里 使用ID = 1和2作为变量，您应该知道该变量并将其从某个地方传递出去.

You could try something like this with a PL/SQL block and Dynamic CURSOR.Note that here I am using ID = 1 and 2 as a variable which you should know and be passing from somewhere.

DBMS_SQL.RETURN_RESULT(12c及更高版本)用于显示动态构造的游标的输出.另外，您可以使用PRINT命令从REFCURSOR获取并显示o/p.

DBMS_SQL.RETURN_RESULT ( 12c and above ) is used to display the output from the dynamically constructed cursor. Alternatively, you could use PRINT command to fetch and display the o/p from REFCURSOR.

SET serveroutput ON
DECLARE
  v_ref SYS_REFCURSOR;
  id1 X.ID%TYPE := 1;
  id2 X.ID%TYPE := 2;
  v_col  VARCHAR2(32);
  v_cols VARCHAR2(1000);
BEGIN
  FOR r IN
  ( SELECT column_name FROM USER_TAB_COLUMNS WHERE table_name = 'X'
  )
  LOOP
    EXECUTE IMMEDIATE ' 
SELECT 
CASE                 
WHEN a.'||r.column_name|| ' = ' || 'b.'|| r.column_name ||'                               
THEN a.'||r.column_name || ' END                     
FROM                           
X  a                
CROSS JOIN X b WHERE 
a.ID = :Id1 AND b.ID = :Id2' INTO v_col USING id1,id2 ;
    v_cols := v_cols ||
    CASE
    WHEN v_col IS NULL THEN
      ','||r.column_name
    END;
  END LOOP;
  v_cols := TRIM(BOTH ',' FROM v_cols);
  OPEN v_ref FOR 'select ' || v_cols || '  FROM X WHERE ID =  '||id1||'                
UNION ALL   select ' || v_cols || '  FROM X WHERE ID =  '||id2 ;
  DBMS_SQL.RETURN_RESULT(v_ref);
END;
/

---

ResultSet #1


ID                 CITY      ZIP          SEGMENT_ONE 
------------------ --------- ------------ ----------- 
1                            14228        X71         
2                  JamesTown 14845        X72         


PL/SQL procedure successfully completed.

这篇关于查找表中两行之间的差异.甲骨文的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！