SQL Server - 针对两列中的重复值进行反向验证 [内部示例]

2021-09-10 00:00:00 sql tsql sql-server ssms

我正在尝试实施验证以检查是否以相反的顺序输入了相同的外汇汇率值.用户将填充由三列组成的电子表格:

I am trying to implement validation to check whether the same FX rate value has been entered in reverse order. Users would populate a spreadsheet consisting of three columns:

  • 来自货币
  • 到货币
  • 评价

用户将数据添加到电子表格中以通过系统导入，以下是数据外观的模拟示例:

The user would add data into a spreadsheet to import through the system, below is a mock example of how the data would look:

我已经有了验证的结构，我只是想知道如何编写 select 语句以返回所有具有两个方向的行(如本例中的前两行)，因为它们是相同的货币，只是顺序相反.

I have the structure of the validation in place, I am just wondering how I would write a select statement in order to return all rows which have both directions (like the first two rows in this example), due to them being the same currency, just in reverse order.

推荐答案

嗯，你可以使用:

select fx.*
from fx
where exists (select 1
              from fx fx2
              where fx2.fromc = fx.toc and fx2.toc = tx.fromc and fx2.rate = fx.rate
             );

实际上，您可以扩展此检查以确保这些值接近于彼此的算术倒数.因为十进制值有一些松弛，我建议:

Actually, you might extend this check to be sure the values come close to being arithmetic inverses of each other. Because there is some slack with decimal values, I would suggest:

select fx.*
from fx
where exists (select 1
              from fx fx2
              where fx2.fromc = fx.toc and fx2.toc = tx.fromc and
                    abs(fx2.rate * fx.rate - 1) > 0.001
             );

这会检查产品的偏差是否超过千分之一.您想要的确切阈值取决于您的需求.

This checks that the product is off by more than one thousandth. The exact threshold you want depends on your needs.

相关文章