获取数字序列中的缺失值

问题描述

我对SQL Server后端进行了以下查询

I made the following query for the SQL Server backend

SELECT TOP(1) (v.rownum + 99)
FROM
    (
        SELECT incrementNo-99 as id, ROW_NUMBER() OVER (ORDER BY incrementNo) as rownum
        FROM proposals
        WHERE [year] = '12'
    )  as v
WHERE v.rownum <> v.id
ORDER BY v.rownum

查找第一个未使用的投标编号.(与上次记录+1无关)

to find the first unused proposal number.(It's not about the lastrecord +1)

但是我意识到访问不支持ROW_NUMBER.我看了看，找不到类似的东西.

But I realized ROW_NUMBER is not supported in access.I looked and I can't find something similar.

有人知道如何在访问中获得与ROW_NUMBER相同的结果吗?

Does anyone know how to get the same result as a ROW_NUMBER in access?

也许有更好的方法可以做到这一点.

Maybe there's a better way of doing this.

实际上，人们在没有限制的情况下插入了他们的提案编号(incrementID).这个数字看起来像是13-152. xx-是当前年份，-xxx是投标编号.最后3位数字应该是递增的，但在某些情况下，一年可能需要10次，因此必须跳过一些数字.这就是为什么我没有自动增量的原因.

Actually people insert their proposal No (incrementID) with no constraint. This number looks like this 13-152. xx- is for the current year and the -xxx is the proposal number. The last 3 digits are supposed to be incremental but in some case maybe 10 times a year they have to skip some numbers. That's why I can't have the auto increment.

所以我执行此查询，以便当他们打开表单时，默认数字是第一个未使用的.

So I do this query so when they open the form, the default number is the first unused.

工作原理:

因为数字从100开始，所以我做-99，所以从1开始.

Because the number starts at 100, I do -99 so it starts at 1.

然后我将行号与id进行比较，因此看起来像这样

Then I compare the row number with the id so it looks like this

ROW NUMBER      |       ID
1                        1                 (100)
2                        2                 (101)
3                        3                 (102)
4                        5                 (104)<--------- WRONG
5                        6                 (105)

所以现在我知道我们跳过4.所以我返回(4-99)= 103

So now I know that we skip 4. So I return (4 - 99) = 103

如果有更好的方法，我不介意更改，但我真的很喜欢此查询.

If there's a better way, I don't mind changing but I really like this query.

如果真的没有其他方法，并且我无法在访问中模拟行号，我将使用直通查询.

If there's really no other way and I can't simulate a row number in access, i will use the pass through query.

谢谢

推荐答案

从您的问题来看，您似乎正在寻找数字序列中的空白，所以:

From your question it appears that you are looking for a gap in a sequence of numbers, so:

SELECT b.akey, (
    SELECT Top 1 akey
    FROM table1 a
    WHERE a.akey > b.akey) AS [next]
FROM table1 AS b
WHERE (
    SELECT Top 1 akey
    FROM table1 a
    WHERE a.akey > b.akey) <> [b].[akey]+1
ORDER BY b.akey

其中table1是表，akey是序列号.

Where table1 is the table and akey is the sequenced number.

这篇关于获取数字序列中的缺失值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！