如何比较表的行与表的所有其他行？

I would like to create a script that takes the rows of a table which have a specific mathematical difference in their ASCII sum and to add the rows to a separate table, or even to flag a different field when they have that difference.

我想创建一个脚本，该脚本获取一个表的行，这些行在其ASCII总和中具有特定的数学差异，并将行添加到单独的表中，或者甚至在它们具有该差异时标记不同的字段。

For instance, I am looking to find when the ASCII sum of word A and the ASCII sum of word B, both stored in rows of a table, have a difference of 63 or 31.

例如，我希望找到存储在表的行中的字A的ASCII和和字B的ASCII和之间的差异为63或31。

I could probably use a loop to select these rows, but SQL is not my greatest virtue.

我可能会使用循环来选择这些行，但SQL不是我最大的优点。

ItemID | asciiSum |ProperDiff
-------|----------|----------
1      | 100      |
2      | 37       |
3      | 69       |
4      | 23       |
5      | 6        |
6      | 38       |

After running the code, the field ProperDiff will be updated to contain 'yes' for ItemID 1,2,3,5,6, since the AsciiSum for 1 and 2 (100-37) = 63 etc.

运行代码后，字段ProperDiff将更新为包含项目ID 1,2,3,5,6的“是”，因为AsciiSum为1和2（100-37）= 63等。

1 个解决方案

#1

This will not be fast, but I think it does what you want:

这不会很快，但我认为它符合您的要求：

update t
    set ProperDiff = 'yes'
    where exists (select 1
                  from t t2
                  where abs(t2.AsciiSum - t.AsciiSum) in (63, 31)
                 );

It should work okay on small tables.

它应该适用于小桌子。

#1