sql语句从datatase中检索最后一个条目值 [英] sql statement to retrieve the last entry value from datatase

问题描述

我的汽车编号有2个部分

3位数是序列号，最后8位数是发票的月份和年份



例如001072013



所以如果我想要下一个InvoiceNo



i从数据库的发票号码的最后一个条目中选择这个



从BuyInvoice选择TOP 1 InvoiceNo



减少前3位数

并且增加

并将其与发票的月份和年份相结合



所以我将是002072013



问题是上面的语句没有返回最后一个条目值

my auto number has 2 parts
3 digits are serial number and last 8 digits are month and year of Invoice

for example 001072013

so if i want a next InvoiceNo

i select from last entry of invoice number from database like this

"select TOP 1 InvoiceNo from BuyInvoice"

cut the first 3 digits
and make increment
and combine it to month and year of Invoice

so i would be 002072013

the problem is the above statement doesn't return the last entry value

推荐答案

测试它：

Test it:

DECLARE @tmp TABLE (InvoiceNo VARCHAR(20))

INSERT INTO @tmp (InvoiceNo)
SELECT '001062013'
UNION ALL SELECT '002062013'
UNION ALL SELECT '003062013'
UNION ALL SELECT '004062013'
UNION ALL SELECT '038062013'
UNION ALL SELECT '001072013'
UNION ALL SELECT '005072013'
UNION ALL SELECT '009072013'
UNION ALL SELECT '011072013'
UNION ALL SELECT '021072013'
UNION ALL SELECT '033072013'

SET DATEFORMAT dmy;

SELECT TOP(1) InvoiceNo
FROM (
	SELECT CONVERT(INT, LEFT(InvoiceNo,3)) AS InvoiceNo, CONVERT(NVARCHAR(10), '01/' + LEFT(RIGHT(InvoiceNo,6),2) + '/' + RIGHT(InvoiceNo,4),121) AS [Date]
	FROM @tmp
	) AS T
ORDER BY [Date] DESC, InvoiceNo DESC

您可以在表格中添加自动编号并选择发票编号使用自动编号作为降序。



从表1中选择前1名InvoiceNo按顺序排序

you can add a auto number to the table and select Invoice number using Auto number as descending order.

select top 1 InvoiceNo from Table_1 order by Sno desc

试试这个。

Try this one.

select TOP 1 InvoiceNo from BuyInvoice order by InvoiceNo desc 

这篇关于sql语句从datatase中检索最后一个条目值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！