mysql - MySQL发票编号范围与计数

Question

首先，我希望这完全是通过MySQL查询完成的。

我有一系列的发票编号

invoice_number
INV001
INV002
INV003
INV004
INV005
001
002
003
006
007
009
010
INVOICE333
INVOICE334
INVOICE335
INVOICE337
INVOICE338
INVOICE339
001INV
002INV
005INV
009INV

我想输出这样的东西

from_invoice_no    to_invoice_no    total_invoices
INV001             INV005           5
001                010              7
INVOICE333         INVOICE339       6
001INV             009INV           4

发票编号格式无法固定。他们将来会改变

请帮助我实现这一目标。

提前致谢。

Answer 1

我将首先展示如何解决此问题的一般思路，并提供一些难看但易于理解的代码。然后，我将解释问题是什么以及如何解决它们。

步骤1：得出分组标准

对于第一步，我假设您具有权限（特权）在表中创建其他列。让我们将其命名为invoice_text。现在，一般的想法是从发票编号中删除所有数字，以便仅保留“文本模式”。然后我们可以按文本模式分组。

假设您已经创建了上面提到的列，则可以执行以下操作：

UPDATE Invoices SET invoice_text = REPLACE(invoice_number, '0', '');
UPDATE Invoices SET invoice_text = REPLACE(invoice_text, '1', '');
UPDATE Invoices SET invoice_text = REPLACE(invoice_text, '2', '');
...
UPDATE Invoices SET invoice_text = REPLACE(invoice_text, '9', '');

完成此操作后，您将在 invoice_text中使用纯数字模式而不包含数字，并可以将其用于分组：

SELECT COUNT(invoice_number) AS total_invoices FROM Invoices
  GROUP BY invoice_text

很好，但这还不是您想要的。它不显示每个组的第一个和最后一个发票号。

步骤2：得出每个组的第一张发票和最后一张发票

对于此步骤，在表中再创建一列。让我们将其命名为 invoice_digits。顾名思义，该名称仅包含纯发票编号，而不包含“样式文本”。

假设您拥有该列，则可以执行以下操作：

UPDATE Invoices SET invoice_digits = REPLACE(invoice_number, 'A', '');
UPDATE Invoices SET invoice_digits = REPLACE(invoice_digits, 'B', '');
UPDATE Invoices SET invoice_digits = REPLACE(invoice_digits, 'C', '');
...
UPDATE Invoices SET invoice_digits = REPLACE(invoice_digits, 'Z', '');

现在，您可以使用该列获取最小和最大发票编号（不带“样式文本”）：

SELECT
  MIN(invoice_digits) AS from_invoice_no,
  MAX(invoice_digits) AS to_invoice_no,
  COUNT(invoice_number) AS total_invoices
FROM Invoices
GROUP BY invoice_text

问题及其解决方法

1）根据您的问题，您想要获得最小和最大完整发票号文本。上面的解决方案将仅显示最小和最大发票编号文本，不包含文本部分，即仅显示数字。

我们可以通过做进一步的 JOIN来解决这个问题，但是由于我可以很好地想象您不会坚持这样做:-)，并且由于它不会使总体思路更加清晰，因此我将其留给您。如果您有兴趣，请告诉我们。

2）可能很难确定数字（即实际发票号）是多少。例如，如果您有 INV001， INV002之类的发票编号，这没问题，但是如果您有 INV001/001， INV001/002， INV002/003等怎么办？在此示例中，我的代码将产生 001001， 001002， 002003作为实际发票编号，并使用它们来确定最小和最大编号。

在这种情况下，这可能不是您想要执行的操作。解决此问题的唯一方法是，您彻底考虑应该考虑的数字，而不是数字，并相应地修改我的代码。

3）我的代码当前使用字符串比较来获取最小和最大发票编号。除了将值与数字进行比较之外，这可能会产生其他结果。如果您想知道这是什么意思：将 '19'与 '9'作为字符串进行比较，并将 19与 9作为数字进行比较。

如果这是一个问题，请使用MySQL的 CAST将文本转换为数字，然后再将其输入 MAX或 MIN。但是请注意，这有其自身的警告：

如果您的发票号非常长且位数太多，以致它们不适合MySQL的数字数据类型，则此方法将失败。如果您将 /之类的字符定义为数字（由于2中描述的问题），也会失败，因为MySQL无法将其转换为数字。

除了转换为数字外，还可以使用前导零填充 invoice_digits中的值，例如使用MySQL的 LPAD函数。这样可以避免上述问题，并且即使它们包含诸如 /之类的非数字，也可以按预期对数字进行排序，但是您必须提前知道数字字符串的最大长度。

4）代码很丑！您是否真的必须通过执行 A语句来逐位删除 Z到 UPDATE的所有可能字符，以获得数字字符串？

实际上，情况甚至更糟。我只是假设您的发票中只有“文本字符” A至 Z。但是Unicode可以定义任何字符：俄语或中文，特殊字符，换句话说：成千上万个不同的字符。

不幸的是，对于AFAIK，MySQL仍然不提供REGEX-REPLACE函数。除非您使用适当的UDF（用户定义的函数）扩展MySQL，否则我看不到有解决任何问题的机会。有一些很酷的人已经意识到了这个问题，并已将此类功能添加到MySQL中。由于建议库似乎不建议使用SO，因此只用google进行“ mysql regex replace”。

以这种方式扩展MySQL时，您可以替换一堆难看的 UPDATE语句，这些语句将发票编号中的数字/文本删除为一个（使用REGEX，您可以一次替换所有数字或所有非数字） ）。

为了完整起见，您可以通过执行 UPDATE来避免使用许多 UPDATE ... SET ... = REPLACE(REPLACE(REPLACE(...)))语句，从而使用一条语句来应用所有更新。但这更加丑陋且容易出错，因此，如果您认真对待自己的问题，则实际上必须通过REGEX-REPLACE扩展MySQL。

5）该解决方案仅在您有权在表中创建新列时才有效。

对于原样的解决方案，这是正确的。但是我之所以选择这种方式，仅仅是因为它使总体思路清晰易懂。除了在原始表中添加列之外，您还可以创建一个新表来存储纯文本/数字（该表可能是临时表）。

此外，由于MySQL支持按计算值分组，因此您根本不需要其他列/表。您应该自己决定最好的方法。