使用字段寻址游标。(SQL Server)

发布于 2024-07-29 05:43:21 字数 482 浏览 6 评论 0原文

我有一个使用游标处理的表。可以说它的结构是这样的：摆脱| 学校 | 订单| 文本

现在，我过滤掉其他学校（因此只显示我的学校），然后按顺序排序，以便我按照我想要的方式排列文本。现在，我的问题是，顺序不是直接递增的（尽管每个学校都是唯一的）它可以从 1,2,3,200,823 等跳过，但我需要能够基本上以

SELECT * FROM SCHOOL_DATA WHERE ORDER=@order

原始顺序执行订单价值。使用游标（甚至使用 ABSOLUTE），它会展平表，以便当 ORDER 字段跳出位置（从 1 到 2 到 100）时，它认为 ORDER=100 是其内部表中的第 3 行，这是应该的。

但是是否可以使用游标来解决这样的问题，以便我可以

FETCH ABSOLUTE 100..

实际获取与 ORDER=100 对应的行？

原文

I have a table that I process using a cursor. Lets say it's structure is like this:
RID | School | Order | Text

Now, I filter out the other schools(so only mine is shown) and then I ORDER BY order, so that I get the text arranged how I want. Now, my problem is, the order isn't straight incrementing(though all of them are unique per school) it can skip from going 1,2,3,200,823 etc. but I need to be able to basically do a

SELECT * FROM SCHOOL_DATA WHERE ORDER=@order

with order being the original order value. With cursors(even using ABSOLUTE) it flattens the table so that when an ORDER field skips out of place(from 1 to 2 to 100) it thinks that ORDER=100 is the 3rd row in it's internal table, as it should.

But is it possible to address such a thing with cursors so that I can do

FETCH ABSOLUTE 100..

and actually get the row corresponding to ORDER=100?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

与风相奔跑 2024-08-05 05:43:21

在我看来，您最好不要使用光标，而只需一次选择一行。基于对 Thorarin 的回答留下的评论。就像是：

declare ... all the variables to hold the different columns.
declare @CurrentOrder ... proper datatype

set @CurrentOrder = ... initial value
while(1=1)
begin
  Select @Var1 = Column1, @Var2 = Column2, etc, @GotoWhere = GotoWhere
  from SCHOOL_DATA
  where Order = @CurrentOrder
  select @RC = @@Rowcount, @E = @@ERROR
  ... do error handling if @RC <> 1 or @E <> 0. Can wrap with try catch if using 2005+
  ... do work.
  ... error out if @GotoWhere is null
  if (... done condition ...)
     exit
  set @CurrentOrder = @GotoWhere
end

It sounds to me like you'd be better of not using a cursor and simply select a row at a time. Based on comments left to Thorarin's answer. Something like:

declare ... all the variables to hold the different columns.
declare @CurrentOrder ... proper datatype

set @CurrentOrder = ... initial value
while(1=1)
begin
  Select @Var1 = Column1, @Var2 = Column2, etc, @GotoWhere = GotoWhere
  from SCHOOL_DATA
  where Order = @CurrentOrder
  select @RC = @@Rowcount, @E = @@ERROR
  ... do error handling if @RC <> 1 or @E <> 0. Can wrap with try catch if using 2005+
  ... do work.
  ... error out if @GotoWhere is null
  if (... done condition ...)
     exit
  set @CurrentOrder = @GotoWhere
end

回复收藏 0 原文

岁月静好 2024-08-05 05:43:21

为了能够做到这一点，您必须创建一个查询，为所有缺失的 Order 值生成空行。似乎是一件愚蠢的事情。

我的印象是，无论你想要实现什么，光标都不是正确的选择，也许你可以更多地解释一下意图，以便人们可以提供可行的替代方案？

例如，将结果放入表变量中，然后在该表中查询正确的订单值似乎是一种替代方案。

DECLARE @school int = 1

DECLARE @schoolData TABLE
(
    RID int,
    School int,
    [Order] int,
    Text ntext
);

INSERT INTO @schoolData 
    SELECT * 
    FROM SCHOOL_DATA
    WHERE School=@school ORDER BY [Order]

SELECT * FROM @schoolData WHERE [Order]=100

然而。我的明显印象是您既不应该使用游标，也不应该使用任何临时存储。

In order to be able do that, you'd have to create a query that generates empty rows for all the missing Order values. Seems like a silly thing to do.

I get the impression that cursors are not the right thing for whatever you're trying to achieve, maybe you could explain a little more about the intent, so people can provide viable alternatives?

For example, putting your results in a table variable, and querying that table for the right Order value seems like an alternative.

DECLARE @school int = 1

DECLARE @schoolData TABLE
(
    RID int,
    School int,
    [Order] int,
    Text ntext
);

INSERT INTO @schoolData 
    SELECT * 
    FROM SCHOOL_DATA
    WHERE School=@school ORDER BY [Order]

SELECT * FROM @schoolData WHERE [Order]=100

However. I get the distinct impression that you should be using neither cursors, nor any temporary storage.

回复收藏 0 原文

~没有更多了~