本文介绍了如何仅获取最新的发票编号的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我有存储为nvarchar(25)的发票编号.

I have Invoice Numbers that are stored as nvarchar(25).

其格式为"#### AA"其中####是发票号,AA是版本号(部分订单发货)我无法更改格式.

Their Format is ‘####AA’Where #### is the Invoice Number and AA is the Version Number (partial Order Shipping)I cannot change the format.

我创建了两个标量函数:

I created two Scalar Functions:

CREATE FUNCTION [dbo].[fnNumbersFromStr](@str varchar(8000))
RETURNS int
AS
BEGIN
  WHILE PATINDEX('%[^0-9]%',@str)> 0
  SET @str = REPLACE(@str, SUBSTRING(@str, PATINDEX('%[^0-9]%', @str), 1), '')
  RETURN CAST(@str AS INT)
END

它是兄弟:

CREATE FUNCTION [dbo].[fnStringFromNum](@str varchar(25))
RETURNS varchar(25)
AS
BEGIN
  WHILE PATINDEX('%[^a-z]%',@str)> 0
  SET @str = REPLACE(@str, SUBSTRING(@str, PATINDEX('%[^a-z]%', @str), 1), '')
  RETURN @str
END

这个脚本让我停滞了:

SELECT
strInvoiceNo,
    dbo.fnNumbersFromStr(strInvoiceNo) AS [InvoiceNumber],
    dbo.fnStringFromNum(strInvoiceNo) AS [InvoiceString]
FROM @TempTable

运行何时返回:


strInvoiceNo    InvoiceNumber    InvoiceString
1000A 1000 A
1000B 1000 B
1000C 1000 C
1001A 1001 A
1001B 1001 B
1002AA 1002 AA
1002AB 1002 AB
1003A 1003 A
1004A 1004 A

我只是无法从这里算出下一步.我被困住了.

I just can’t figure out the next step from here. I am stuck.

我希望选择项仅返回最新的发票版本:

I would like the select to only return the latest Invoice Versions:

1000C
1001B
1002AB
1003A
1004A

1000C
1001B
1002AB
1003A
1004A

Sql,Lamda或Linq对我来说可以正常工作.

Sql, Lamda or Linq will work fine for me.

预先感谢

推荐答案

尝试一下:

SELECT
  InvoiceNumber + MAX(InvoiceString) As strInvoiceNo
FROM
(
   SELECT
       dbo.fnNumbersFromStr(strInvoiceNo) AS [InvoiceNumber],
       dbo.fnStringFromNum(strInvoiceNo) AS [InvoiceString]
   FROM @TempTable
) As tbl
GROUP BY InvoiceNumber

这篇关于如何仅获取最新的发票编号的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

09-06 02:46