Oracle SQL比较表中的记录 [英] Oracle SQL compare records within a table

问题描述

我有一个如下表:

S.No | Item_ID | Item_Revision | Code |
-----+---------+---------------+-------
1.   | item1   | 0             | xyz  |
2.   | item2   | 0             | xyz  |
3.   | item3   | 0             | xyz  |
4.   | item1   | 1             |      |
5.   | item2   | 1             | abc  |
6.   | item3   | 1             | xyz  |

我需要比较表中的记录，以找到项目不同版本中代码的差异.

I need to compare the records in the table to find the differences in code in different revisions of the items.

我希望结果集如下:

 | Item_ID | Code_Revision_0 | Code_Revision_1 |
 | item1   | xyz             |                 |
 | item2   | xyz             | abc             |

我不能为此目的制定一个oracle查询.

I am not able to formulate an oracle query for this purpose.

提前谢谢！

推荐答案

一个基本的想法是使用 join :

One basic idea is to use join:

select t0.item_id, t0.code as code_0, t1.code as code_1
from t t0 join
     t t1
     on t0.item_id = t1.item_id and
        t0.item_revision = 0 and
        t1.item_revision = 1
where t0.code <> t1.code;

但是，如果 code 的值为 NULL (或空字符串)，则需要格外小心:

However, if the code value is NULL (or an empty string), you need to be more careful:

where t0.code <> t1.code or (t0.code is null and t1.code is not null) or
      (t0.code is not null and t1.code is null) 

这篇关于Oracle SQL比较表中的记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！