如何在SQL中解决松散关系
如果你在某些特殊商业问题上有一点创造性思维的话，你就会经常在SQL中设计解决方案。例如，公司为了使索要的费用和支付的费用一致，他们可以在下面两个方法中间选择一个。
第一种方法(加拿大着名的Open Item)意味着你将索要费用与支付费用进行精确的匹配，典型的做法是，将索要费用的数目放到支付表中进行比较。
第二种方法(加拿大着名的Statement)意味着你需要计算所有索要费用的总和与所有支付的总和，然后再将它们进行匹配，当它们不平衡的时候报告它们的区别。第二种方法比较简单，所以我在这里就不举例说明了。这里，我们主要讨论第一种方法。
Open Item方法依据两个假设：认为支付的数目与索要的数目精确匹配，同时认为顾客会在账单上写明索要费用。两个假设都是不可靠的。这将导致职员要对这些不可靠所引起的误差而负责。你可以使用SQL程序来大大减少这种繁杂的工作。
下面这段代码创建了一个计划和两张表。你需要先创建一个测试数据库，然后再运行下面这段SQL语句：
Create Schema Loose
GO CREATE TABLE [Loose].[Charges](
[PK] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
[Amount] [money] NOT NULL
)ON [PRIMARY]
GO CREATE TABLE [Loose].[Payments](
[PK] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
[Amount] [money] NOT NULL
)ON [PRIMARY]
GO
|
现在，将下面这些数据插入到表中，以确保你有多种支付——一些与索要数目精确匹配，一些与索要数目比较接近，还有一些稍微与索要数目有点不同。
INSERT INTO Loose.Charges VALUES (100)
INSERT INTO Loose.Charges VALUES (12)
INSERT INTO Loose.Charges VALUES (56)
INSERT INTO Loose.Charges VALUES (43)
INSERT INTO Loose.Charges VALUES (59)
INSERT INTO Loose.Charges VALUES (998)
GO INSERT INTO Loose.Payments VALUES (99)
INSERT INTO Loose.Payments VALUES (62)
INSERT INTO Loose.Payments VALUES (40)
INSERT INTO Loose.Payments VALUES (50)
INSERT INTO Loose.Payments VALUES (12)
INSERT INTO Loose.Payments VALUES (1000)
GO
|
我们首先要做的事情就是定义要调用的门限值(例如，在我推断支付数目与索要数目的关系之前，必须知道支付数目和索要数目有多接近)。在下面的例子中，我定义门限值为2(在真实的例子中，门限值可以根据实际的环境来设置)。
因为支付的数目可能多，也可能少，还可能与索要的数目一样，为了将支付与索要进行匹配，我必须在某个范围内上下查找索要的数目。你可以使用BETWEEN语句，但是我在这里使用了Abs()函数。这个函数可以更清楚的表达我们感兴趣的值的范围。
DECLARE @proximity int
SET @proximity = 2 -- change this value to suit your situation
SELECT
Loose.Charges.PK AS Charge#,
Loose.Charges.Amount AS Charge,
Loose.Payments.PK AS Payment#,
Loose.Payments.Amount AS Payment
FROM
Loose.Charges INNER JOIN Loose.Payments
ON ABS(Loose.Charges.Amount - Loose.Payments.Amount)<= @proximity
GO
|
在你运行代码之前，你可以先预测一下结果，然后再运行代码，看看你预测的是否正确。
它的结果如下所示：
Charge# Charge Payment# Payment
1 100.00 1 99.00
2 12.005 12.00
6 998.00 6 1000.00
|
虽然这个解决方法是正确的，但是它的应用范围并不是很广泛。这个例子正确的定义了三种情况：低于支付、正确的支付、多于支付。然而，这个方法除去了所有不能与支付匹配的索要，同时工作人员可能对各种情况都感兴趣。我能通过改变连接来得到不同的结果(参考Listing A)。下面就是这个过程的运行结果：
Charge#ChargePayment#Payment
1100.00199.00
212.00512.00
356.00NULLNULL
443.00NULLNULL
559.00NULLNULL
6998.0061000.00 |
现在，工作人员就知道有三个索要与支付不匹配了。
如果对于同一个产品，顾客进行了两次错误的支付，且支付的数目一样的话，你应该如何得到这个信息呢?举个例子来说，你可以在Payments表中增加一条数目为00的记录，这时候Payments表中就有两条相同的记录了，然后再重新运行一下最后的查询代码。下面就是它的结果集：
Charge#ChargePayment#Payment
1100.00199.00
212.00512.00
356.00NULLNULL
443.00NULLNULL
559.00NULLNULL
6998.0061000.00
6998.0071000.00 |
这个结果集显示了一个解决隐藏问题的方法。我可以从这个结果中看出索要数目为6的记录被支付了两次。
本文作者:
«
»