为组合框查找添加一些 AI

引言

如果您在数据库应用程序上手动输入大量内容，您可能会欣赏组合框的自动完成功能。与默认的 Windows 组合框控件的首字母响应不同，市面上有各种各样的组合框实现。您可以找到一些非常出色但免费的实现，从纯 JavaScript 到 AJAX 控件；每个项目都有复选框或单选按钮、图标或图像、多列、多首字母匹配、颜色（或格式）分组以及任意位置字母匹配（带高亮显示）。

我的需求相对简单；我想要 Access 风格的组合框，它允许首字母匹配和多列，并且我希望第一个匹配项是最常用的或最可能的匹配项。这可以通过 SQL 来严格实现。

为了说明，假设您需要从 HR 的 John Smith、HR 的 Jane Smith 和 Sales 的 John Smith 中选择一名员工。

第一种方法是查询员工表，并将三个列（姓、名、部门）打包（连接）到一个单一的（按字母顺序排序的）列中，得到

Smith Jane HR
Smith John HR
Smith John Sales

键入“Smith J”后，“Jan”将显示在组合框中。我希望“John”首先显示，仅仅因为 John 比 Jane 多。

第二种方法是将字段保留在不同的列中。现在，排序子句使用这三个列

Smith | Jane | HR
Smith | John | HR
Smith | John | Sales

这并没有实现太多功能，但通过添加姓和名的频率计数（即员工表中 John Smiths、Jane Smiths 等有多少），事情开始变得有希望了

Smith | 1 | Jane | HR
Smith | 2 | John | HR
Smith | 2 | John | Sales

通过发出带有频率列降序排列的新排序子句，我们可以得到

Smith | 2 | John | HR
Smith | 2 | John | Sales
Smith | 1 | Jane | HR

现在，键入“Smith J”后，第一个 John 将会出现。这至少可以节省我按一次向下箭头键。

实现

如前所述，这只是一个示例。我的实际应用程序涉及两个具有一对多关系的表。我有一个存储表（零售商、供应商、维修店等）和一个与这些存储相关的收据表。由于许多商店实际上是零售或服务连锁店，我将商店名称和编号保存在不同的字段中。最初，这有助于我独立于访问的 7-Eleven 来汇总（分组）我的“7-Eleven”支出。

我需要的是历史上访问次数最多的商店按降序排列。这是用于输入收据表中新收据的原始商店组合框的部分显示

Store    Description    Address
346    7-Eleven 24625    3230 W DAVIS ST
341    7-Eleven 26264    100 N GREENVILLE AVE
132    7-Eleven 32870    1004 W MCDERMOTT R
171    7-Eleven 33302    10565 CUSTER RD
15     7-Eleven 33690    7818 ROWLETT RD
12     7-Eleven 33697    3975 LAKE FOREST

“Store”列是唯一 ID，“Description”列连接了商店名称和编号，第三列只是一个记忆辅助。这是用作组合框记录源的 T-SQL 语句

SELECT   Store,
         Name & ' ' & Number AS Description,
         Address

FROM     Stores

ORDER BY Name, Number;

为了在排序子句中使用访问频率，我们首先需要将名称和编号分开，就像在示例中一样

SELECT   Store,
         Name,
         Number,
         Address

FROM     Stores

ORDER BY Name, Number;

现在我们可以将此查询（称之为q1）作为分组查询的源，该查询计算每个商店的收据数

SELECT   Store,
         Name & ' ' & Number AS Description,
         Address

FROM     q1 LEFT JOIN Receipts ON q1.Store = Receipts.Store

GROUP BY Store, Name, Number, Address

ORDER BY Name, COUNT(Receipts.Receipt) DESC, Number;

查询产生以下结果

Store  Description     Address               CountOfReceipt
15     7-Eleven 33690  7818 ROWLETT RD       30
346    7-Eleven 24625  3230 W DAVIS ST       1
341    7-Eleven 26264  100 N GREENVILLE AVE  1
132    7-Eleven 32870  1004 W MCDERMOTT R    1
171    7-Eleven 33302  10565 CUSTER RD       1
12     7-Eleven 33697  3975 LAKE FOREST      1

从技术上讲，没有分组，因为使商店唯一的字段都参与了分组子句。另外请注意，我们仍然使用原始的商店名称和编号连接；编号只需要参与排序子句。

收据计数仅用于说明目的。尽管列表乍一看可能会反直觉或混乱，但很可能访问次数最多的 7-Eleven 是我接下来要访问的。这将节省我显示列表，以及节省键入或向下滚动列表查找正确选择的时间。节省是渐进的，也就是说，当另一个 7-Eleven 获得一次访问时，它将出现在列表的第二位。保持按商店编号排序也很重要，这样当两个或多个商店的收据计数相同时，它就会优先，如上一个样本所示。

顺便说一句，并非所有商店都有商店编号，有些“编号”实际上是字母数字，例如“3A”或“II”。让我们看看如何处理这些。

在数据库术语中，商店可能是唯一的（例如，“Lake View Florist”只有一个记录）。一些连锁店保留其内部的商店编号；收据或其网站上没有提示。在这种情况下，商店编号将留空（值为NULL），并在描述字段周围添加一些“立即 if”逻辑，以避免连接失败（并以便进行修剪）。如果您需要详细信息，这是描述列的 T-SQL（Access/VB）版本

Description: q1.Name & IIf(IsNull(q1.Number),'',' ' & q1.Number)

关于字母数字商店编号，我们对此无能为力。如果您将商店编号字段设置为严格的数字字段，您将不得不为那些使用字母数字商店编号（代码）的商店设计自己的映射。如果您将其保留为文本字段，您将不得不忍受一些异常，例如 Cici's Pizza 7006 显示在 Cici's Pizza 94 之前，仅仅因为它们具有相同的收据数量并且它们的输入顺序是这样的（商店 ID 是提示）

Store    Description         Address                   CountOfReceipt
84       Cici's Pizza 181    3520 LAKEVIEW PKWY        6
85       Cici's Pizza 1      2936 LAVON DR             3
87       Cici's Pizza 10     2220 COIT RD STE 300      2
86       Cici's Pizza 7006   1105 E PARKER RD STE 112  1
340      Cici's Pizza 2      N GREENVILLE AVE          1
360      Cici's Pizza 94     479 E I30                 1

您可以为商店编号添加前导零，或者在q1中使用另一个表达式执行升序数字排序

Number1: Val(Nz([Number],0))

Nz函数用0替换null，Val函数处理“3A”和“II”。不幸的是，这不是一个完美的解决方案；“3A”将被转换为3，“II”将被转换为0。此外，如果商店以字母字符开头（例如，“A1”、“B3”等）进行编码，则这些代码也将在此方法中归零。我尝试过一些额外的逻辑但没有成功。如果您有解决方法，请告诉我；目标是保留以字母开头的字符串，并将以数字开头的字符串转换为实际的数值。

到目前为止，前面的表达式实现了频率排序，并以正确的商店编号排序作为备用

Store    Description        Address                     CountOfReceipt
84       Cici's Pizza 181   3520 LAKEVIEW PKWY          6
85       Cici's Pizza 1     2936 LAVON DR               3
87       Cici's Pizza 10    2220 COIT RD STE 300        2
340      Cici's Pizza 2     N GREENVILLE AVE            1
360      Cici's Pizza 94    479 E I30                   1
86       Cici's Pizza 7006  1105 E PARKER RD STE 112    1

这是最终的q1 T-SQL 语句

SELECT   Store,
         Name,
         Val(Nz([Number],0)) AS Number1,
         Address

FROM     Stores

ORDER BY Name, Val(Nz([Number],0));

以及组合框的记录源

SELECT   Store,
         Name & IIf(Number1=0,'',' ' & Number1) AS Description,
         Address

FROM     q1 LEFT JOIN Receipts ON q1.Store = Receipts.Store

GROUP BY Store, Name, Number1, Address

ORDER BY Name, COUNT(Receipts.Receipt) DESC, Number1;

您看，这就是一个用于一对多关系（例如，订单和订单明细、产品和费用等）的频率计数（或可能性）排序方法。一点点人工智能永远不会伤害任何人；大多数用户可能一开始不欣赏它，但每天查找两三百次后，他们可能会！

关注点

我试图让 T-SQL 语句尽可能通用；如果您实际在 MS Access 中使用代码，请确保在 VB 函数中使用双引号而不是单引号。

历史

First version.