Mad MAX() 背后的逻辑

Mad MAX() 背后的逻辑

本文重点介绍如何在混合使用的 CHAR 列中分配下一个连续的客户编号。F1:'Books Online' 的解释毫无用处，而且我在 MSDN 或网上都找不到关于此问题的信息。

如果你们中有人尝试过使用 TSQL MAX() 函数对文本列中的数字进行排序，你们就会知道。但我为一个项目损失了将近一周的时间，因为这种特殊的 MAX 函数的工作方式与其它编程语言中的 MAX() 有些不同。不只是语法——底层是完全不同的。我最终消耗了近 30 个计费小时。有整整两天的时间，我都在深入地学习这个小麻烦的细微差别。

最近，一家客户委托我们集成其网站购物车和会计系统。也许“集成”这个词不太恰当，因为他们希望降低成本；他们不关心是否是实时；但它需要“傻瓜式”操作。换句话说：当我们说“有三种方法可以做到：便宜、快、好：任选两种”时，他们说他们想要“全部三种”。我讨厌这种情况发生……

为了保持相对简单（快），我们决定让网站开发人员创建一个 CSV 文件，我们可以定期导入。我们不想重复造轮子（便宜），所以我们采用了现成的 AccountMate 导入销售订单表单，并根据他们的需求进行了修改。基本上，我们简化了旨在导入多种格式的用户界面，并创建了一个“原始人都能用”（好）的新界面。我们称之为“Web 导入向导”。详细规格见下文。

本质上，我们正在构建一个应用程序，用于从网站购物车导入数据，以便用户可以发送销售订单（SO）来创建应收账款（AR）发票，而不是手动输入。这还将创建拣货单、装箱单并简化库存控制（IC）功能。会计系统框架是最新版本的 AccountMate for MS-SQL，因此前端是用 Visual FoxPro v9（VFP）编写的，而且幸运的是，客户还拥有 SO、AR 和 IC 源代码的许可以及远见。框架的后端是 MS-SQL2005。前面提到的所有报告都在 Crystal XI 中完成。

首先，我们需要选择导入文件并解析它。我们查看了用户的电子邮件地址是否在系统中。如果在，我们将使用现有的客户编号；否则，我们将创建一个新的客户编号，相应地填充或更新客户记录（账单地址、送货地址等），然后处理明细项，再处理下一条记录。当我们不得不创建新客户记录时——事情变得有点棘手。

当 FoxPro 开发人员修改了屏幕表单时，他们在 FoxPro 中创建了一个名为 GetNewCustNo() 的函数，用于获取下一个连续的客户编号。这个函数需要做的事情相对简单

1. 从名为 arcust 的表中获取最大的数字客户 ID (cCustNo)
2. 如果为空，则默认值为 '100000'
3. 将该 CHAR 数据转换为 Numeric 数据类型，以便我们可以进行数学运算
4. 将其加 1
5. 将其转换回 CHAR

与许多其他现代语言一样，VFP 具有 SQL 通道函数，因此我们可以在我们的 shell 中执行 TSQL。这是 VFP 代码

local lcCustNo 
if GetSqlData("SELECT isnull(Max(cCustNo)),'') as cCustNo " + ; 
    "FROM arcust ", "CurArcust") &&, lnHandle) 
  lcCustNo = iif(CurArcust.cCustNo = ' ','100000',alltrim(str(val(CurArcust.cCustNo) + 1))) 
else 
  lcCustNo = '100000' 
endif 
return lcCustNo 

这效果很好，但前提是我们从一个空的客户表开始——当我们在生产环境中部署时，它就崩溃了：主键冲突！显然，现有的客户数据存在问题，但 TSQL MAX() 函数也存在一些奇怪的问题——经过数小时的故障排除，我注意到有些 custNo 不是数字——嗯，这就是主键冲突的原因。我将通道查询插入到 Query Analyzer 中，发现它总是返回 '9999'，这是一个客户正在使用的测试公司。最让我困惑的是，有一个客户编号是 '63492'。为什么它找不到它？

长话短说：我调整了 VFP GetNewCustNo() 方法，并将 ccustno 转换为整数（实际上是 CAST ccustno AS INT），然后通过 VFP 中的 SQL 通道转换回 CHAR。

基本上，我只是将第一行更改为这样

if GetSqlData("SELECT CAST(isnull(Max(CAST(cCustNo as INT)),'') as char(10)) as cCustNo 
    FROM arcust WHERE isnumeric(cCustNo) = 1", "CurArcust") , lnHandle) 

这奏效了。但是，我现在真的很困惑。我不确定它为什么起作用，因为我不明白它为什么一开始会出问题。所以，我还是在生产数据上测试了它。好吧——在两个示例公司中一切都运行正常……但不是在生产环境中。我感觉就像查理·布朗，露西正在拿着足球。啊啊啊啊！

再次，主键冲突——我正在考虑“客户编号不连续”和/或“它们不是都是数字”（但我已经用 isNumeric=1 删除了字母数字字符，不是吗？）。唯一剩下的就是我们用来获取 str(val(MAX(arcust.ccustno)))+1 的客户端代码在处理一个名为“TEST”的公司时崩溃了，但这毫无意义，因为示例公司在 ccustno 中也使用了字母数字字符。嗯……

毫无疑问，现有的客户数据存在问题，但 TSQL MAX() 函数也存在一些非常奇怪的问题——经过更多的故障排除，我注意到所有 custNo 都被输入为 '100001'——嗯，这就是主键冲突。我将通道查询插入到 Query Analyzer 中，发现它总是返回 ' '，这没有任何意义。它为什么会失败？

尽管我多么希望将责任归咎于客户或数据，但我不能。这甚至不是 Microsoft 的 bug！在 sql2005 中也是如此，而且似乎是故意设计的。事情是这样的……当 SQL 比较不同长度的文本字符串的值时，它只查看第一个 n 个字符（其中 n 是系列中最小的长度）——在去除左右两侧的空格之后。

为了证明这一点，请尝试运行以下 TSQL

IF '9999' > '63492' PRINT 'THIS APPEARS TO DEFY LOGIC!' 

就是这样！我有了顿悟尤里卡！——这就是问题所在……

它不是在比较数字——它是在对文本进行排序！

如果你在给列表按字母排序，这是完全有道理的；“zylch”大于“zoo”，但它们都大于“abracadabra”——即使它们的长度都不同。这是一个标准的冒泡排序——所以，它实际上一次只比较两个。

现在，当我们确实想比较（存储在混合使用列中的）字符值的（已转换为数字的）等效值时，这真的很糟糕，除非你：投入大量代码（例如；用零左填充；转换为或转换到某种数字数据类型，然后进行比较，然后再转换回字符数据类型——这就是我之前所做的）。但是，这可能导致各种其他问题，例如：如果由于非法字符而无法转换怎么办？我认为：SQL 将根据字符串尝试先执行数学运算，然后将结果转换为 char，或者完全失败。再次……太多的代码了！我宁愿浪费时间写冗长的解释……（比如这篇！）

这是我提出的方法

SELECT STR(isNull(Max(CAST(cCustNo AS Int)+1),'100001')) as cCustNo
  FROM arcust WHERE isNumeric(cCustNo)=1 

太棒了！我甚至消除了条件 IF 和 IIF 语句。这奏效了，但如果有人在字符字符串中加入句点，它就会失败（无法将 Float 转换为 Integer）。所以……我对其进行了修改，如下所示

SELECT STR(isNull(Max(CAST(cCustNo AS Decimal(10,0))+1),'100001')) 
    AS cCustNo FROM arcust WHERE isNumeric(cCustNo)=1 

我认为这是一个全垒打，因为它纯粹是 TSQL，并且始终返回增 1 的最大整数值——即使有人创建了带有句点或其他字符的客户号。作为奖励，它可以让我们用一行代码替换整个 GetNewCustNo() 函数。它甚至可以作为 INSERT INTO 的一部分，从而消除两个用户几乎同时运行代码时发生客户号冲突的可能性。这一切都可以在服务器端完成——正如它应该的那样。——无论我是在 VFP、VB、C 还是 Java 的任何一种语言中编写。

我有一种奇怪的感觉，可能还有其他类型的无效字符数据会渗入并导致其失败。但这将是另一篇文章……

当然，客户不会为将此功能回溯到其现有数据所花费的计费小时数付费。所以，你们可以免费获得这个教训。如果它能为一个人节省一个小时，我将通过业力得到回报。故事的寓意是：不要做我做过的！

~wiz