SQL - 如何选择列中的下一个值？

In SQL Server 2008 r2 I have a column 'Activity_ID' that's an integer and can have a range of values (i.e. 20, 21, 22, 28, 29, 37, 38, 41).

在SQL Server 2008 r2中,我有一个“Activity_ID”列,它是一个整数,可以有一系列值(即20,21,22,28,29,37,38,41)。

I have no way of knowing what Activity_ID will be next.

我无法知道接下来会有什么Activity_ID。

Is there any way to check for the next highest Activity_ID (and then do X once I have found it)?

有没有办法检查下一个最高的Activity_ID(然后我找到它后再做X)?

M

2 个解决方案

#1

In SQL Server 2012 or later, you would use lag or lead. In 2008, you can simulate the same behavior with row number. I am guessing here because your question didn't come with sample data, but it might look something like this:

在SQL Server 2012或更高版本中,您将使用lag或lead。在2008年,您可以使用行号模拟相同的行为。我在这里猜测是因为你的问题没有提供样本数据,但它可能看起来像这样:

;with CTE as 
  (select *
  , Row_Number() over (partition by Name order by ActivityCode) as RN 
  from MyTable)

select a.*, b.activitycode as NextHighestCode 
from CTE a
left join CTE b
on a.name = b.name
and a.RN = b.RN - 1

This will return the values in your table (replace name with whatever actual columns you have), and the next highest activity code for each row. This may not handle ties in the way that you would expect, so you may want to add more columns to the partition clause if you are expecting them.

这将返回表中的值(将name替换为您拥有的任何实际列),以及每行的下一个最高活动代码。这可能无法以您期望的方式处理关联,因此如果您期望它们,您可能希望向partition子句添加更多列。

#2

  declare @tb table
  (
    orderNumber int identity(1,1),
    value int
  )

  insert into @tb values(20)
  insert into @tb values(21)
  insert into @tb values(22)
  insert into @tb values(28)
  insert into @tb values(29)
  insert into @tb values(37)
  insert into @tb values(38)
  insert into @tb values(41)



  declare @v table(
    activity_id int
  )

  insert into @v values(37)

    select 
        (select top 1 value from @tb t1 where  t1.value > v.activity_id)
     from @v v