获取两行之间不同的列 [英] Get columns that differ between 2 rows

问题描述

我有一个具有60列的表 company 。目标是创建一个工具来查找，比较和消除此表中的重复项。

I have a table company with 60 columns. The goal is to create a tool to find, compare and eliminate duplicates in this table.

示例：我找到了两家可能相同的公司，但我需要知道为了继续进行操作，这两行之间的值（列）有所不同。

Example: I find 2 companies that potentially are the same, but I need to know which values (columns) differ between these 2 rows in order to continue.

我认为可以逐列比较x 60，但是我搜索了一个更简单的

I think it is possible to compare column by column x 60, but I search for a simpler and more generic solution.

类似的东西

SELECT * FROM company where co_id=22
SHOW DIFFERENCE
SELECT * FROM company where co_id=33

结果应该是不同的列名称。

The result should be the column names that differ.

推荐答案

您可以使用 hstore 扩展名。

You may use the hstore extension for this. It often comes handy when the there's the need to iterate over columns.

技巧是将每一行的内容转换为 column_name = > value 配对为一个hstore值，然后使用hstore函数计算差异。

The trick is to convert, for each row, the contents to column_name=>value pairs into a hstore value, and then use the hstore functions to compute the differences.

Demo：

CREATE TABLE table1 (id int primary key, t1 text, t2 text, t3 text);

让我们插入两行，这两行的主键和另一列不同（ t3 ）。

Let's insert two rows that differ by the primary key and one other column (t3).

INSERT INTO table1 VALUES (
 (1,'foo','bar','baz'),
 (2,'foo','bar','biz')
);

查询：

SELECT skeys(h1-h2) from 
  (select hstore(t.*) as h1 from table1 t where id=1) h1
 CROSS JOIN
  (select hstore(t.*) as h2 from table1 t where id=2) h2;

h1-h2 计算差异键key和 skeys（）将结果作为一组输出。

h1-h2 computes the difference key by key and skeys() outputs the result as a set.

结果：

 skeys 
-------
 id
 t3

可以使用 skeys（（h1-h2）-'id'）完善选择列表，以始终删除 id 作为主键，显然在各行之间总是会有所不同。

The select-list might be refined with skeys((h1-h2)-'id') to always remove id which, as the primary key, will obviously always differ between rows.

这篇关于获取两行之间不同的列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！