我如何仅获取最新的发票号码[英] How do I grab only the latest Invoice Number

本文是小编为大家收集整理的关于我如何仅获取最新的发票号码的处理/解决方法,可以参考本文帮助大家快速定位并解决问题,中文翻译不准确的可切换到English标签页查看源文。

问题描述

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

他们的格式为" #### aa’ 其中####是发票号,AA是版本号(部分订单运输) 我无法更改格式.

我创建了两个标量函数:

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

我只是无法从这里弄清楚下一步.我被卡住了.

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

1000c
1001b
1002AB
1003a
1004a

sql,lamda或linq对我来说很好.

预先感谢,

推荐答案

尝试以下方法:

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

其他推荐答案

我不认为您需要任何UDF,一个简单的窗口功能查询应返回您想要的内容.

WITH x AS
(
 Select *
       ,ROW_NUMBER() OVER (PARTITION BY InvoiceNumber ORDER BY strInvoiceNo DESC) rn 
 FROM TableName
)
SELECT strInvoiceNo, InvoiceNumber, InvoiceString 
FROM X 
WHERE rn = 1

SELECT strInvoiceNo, InvoiceNumber, InvoiceString 
FROM 
(
 Select *
       ,ROW_NUMBER() OVER (PARTITION BY InvoiceNumber ORDER BY strInvoiceNo DESC) rn 
 FROM TableName
)x
WHERE rn = 1

其他推荐答案

这是在Linq中(假设FNStringFromnum返回一个带有空格左侧填充的字符串):

dbContext.YOURTABLE
  .GroupBy(x=>UDFFunctions.fnNumbersFromStr(x.AccountNumber))
  .Select(x=>x.OrderByDescending(y=>UDFFunctions.fnStringFromNum(y.AccountNumber).FirstOrDefault())

sql(使用当前的fnstringfromnum):

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

本文地址:https://www.itbaoku.cn/post/1557044.html

问题描述

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

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

And it’s brother:

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

I am stalled with this script:

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

Which when runs returns:

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

Sql, Lamda or Linq will work fine for me.

Thanks in advance,

推荐答案

Try this:

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

其他推荐答案

I dont think you need any UDF for this, a simple windowing function query should return what you looking for.

WITH x AS
(
 Select *
       ,ROW_NUMBER() OVER (PARTITION BY InvoiceNumber ORDER BY strInvoiceNo DESC) rn 
 FROM TableName
)
SELECT strInvoiceNo, InvoiceNumber, InvoiceString 
FROM X 
WHERE rn = 1

OR

SELECT strInvoiceNo, InvoiceNumber, InvoiceString 
FROM 
(
 Select *
       ,ROW_NUMBER() OVER (PARTITION BY InvoiceNumber ORDER BY strInvoiceNo DESC) rn 
 FROM TableName
)x
WHERE rn = 1

其他推荐答案

Here is it in LINQ (Assuming fnStringFromNum returns a string padded on the left with spaces):

dbContext.YOURTABLE
  .GroupBy(x=>UDFFunctions.fnNumbersFromStr(x.AccountNumber))
  .Select(x=>x.OrderByDescending(y=>UDFFunctions.fnStringFromNum(y.AccountNumber).FirstOrDefault())

SQL (using current fnStringFromNum):

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