如何从上面的行中选择信息?

I want to add a column to my table that is like the following: This is just an example of how the table is structured, the real table is more than 10.000 rows.

我想向表中添加一个列，如下所示:这只是表结构的一个示例，实际的表有超过10.000行。

No_   Name       Account_Type    Subgroup     (New_Column)
100   Sales      3               
200   Underwear  0               250          *100
300   Bikes      0               250          *100
400   Profit     3                             
500   Cash       0               450          *400

So for every time there is a value in 'Subgroup' I want the (New_Column) to get the value [No_] from the row above

因此，每次在'Subgroup'中有一个值时，我都希望(New_Column)从上面的行中获取值[No_]

No_   Name       Account_Type    Subgroup     (New_Column)
100   Sales      3               
150   TotalSales 3
200   Underwear  0               250          *150
300   Bikes      0               250          *150
400   Profit     3                             
500   Cash       0               450          *400

There are cases where the table is like the above, where two "Headers" are above. And in that case I also want the first above row (150) in this case.

有些情况下，表就像上面那样，上面有两个“header”。在这种情况下，我还需要第一行(150)在这个例子中。

Is this a case for a cursor or what do you recommend?

这是光标的情况还是你推荐的?

The data is ordered by No_

数据由No_排序

--EDIT--

——编辑

Starting from the first line and then running through the whole table: Is there a way I can store the value for [No_] where [Subgroup] is ''? And following that insert this [No_] value in the (New_Column) in each row below having value in the [Subgroup] row. And when the [Subgroup] row is empty the process will keep going, inserting the next [No_] value in (New_Column), that is if the next line has a value in [Subgroup]

从第一行开始，然后遍历整个表:是否有一种方法可以存储[No_] where [Subgroup] Is "的值?然后在(New_Column)中插入这个[No_]值，在[子组]行中有值。当[Subgroup]行为空时，进程将继续，在(New_Column)中插入下一个[No_]值，即如果下一行在[Subgroup]中有一个值

Here is a better image for what I´m trying to do: 如何从上面的行中选择信息?

这是一个更好的形象我´试图做什么:

5 个解决方案

#1

The ROW_NUMBER function will allow you to find out what number the row is, but because it is a windowed function, you will have to use a common table expression (CTE) to join the table with itself.

ROW_NUMBER函数将允许您找到该行的编号，但是因为它是一个窗口函数，所以您必须使用一个公共表表达式(CTE)来与它自己连接。

WITH cte AS
(
    SELECT [No_], Name, Account_Type, Subgroup, [Row] = ROW_NUMBER() OVER (ORDER BY [No_])
    FROM table
)
SELECT t1.*, t2.[No_]
FROM cte t1 
LEFT JOIN cte t2 ON t1.Row = t2.Row - 1

Hope this helps.

希望这个有帮助。

#2

SQL Server 2012 suggests using Window Offset Functions. In this case : LAG

SQL Server 2012建议使用窗口偏移函数。在这种情况下:滞后

Something like this:

是这样的:

SELECT [No_]
  ,[Name]
  ,[Account_Type]
  ,[Subgroup]
  ,LAG([No_]) OVER(PARTITION BY [Subgroup]
ORDER BY [No_]) as [PrevValue]
FROM table

Here is an example from MS: http://technet.microsoft.com/en-us/library/hh231256.aspx

这里有一个来自MS的例子:http://technet.microsoft.com/en-us/library/hh231256.aspx

#3

Next query will return Name of the parent row instead of the row itself, i.e. Sales for both Sales, Underwear, Bikes; and Profit for Profit, Cash:

下一个查询将返回父行而不是行本身的名称，即Sales、pants、Bikes;利润和利润，现金:

select ISNULL(t2.Name, t1.Name)
from table t1
left join table t2 on t1.NewColumn = t2.No

#4

So in SQL Server 2008 i created test table with 3 values in it:

在SQL Server 2008中，我创建了一个包含3个值的测试表:

create table #ttable
(
    id int primary key identity,
    number int,
    number_prev int
)
Go
Insert Into #ttable (number) 
Output inserted.id
Values (10), (20), (30);

Insert in table, that does what you need (at least if understood correctly) looks like this:

插入到表中，这样做(至少如果正确理解)是这样的:

declare @new_value int;
set @new_value = 13; -- NEW value
Insert Into #ttable (number, number_prev)
Values (@new_value, 
    (Select Max(number) From #ttable t Where t.number < @new_value))

[This part added] And to work with subgroup- just modify the inner select to filter out it:

[这部分增加]和工作与子组-只是修改内选择过滤它:

Select Max(number) From #ttable t 
Where t.number < @new_value And Subgroup != @Subgroup

#5

SELECT
      No_
    , Name 
    , Account_Type 
    , Subgroup 
    , ( SELECT MAX(above.No_) 
        FROM TableX AS above
        WHERE above.No_ < a.No_
          AND above.Account_Type = 3
          AND a.Account_Type <> 3
      ) AS NewColumn
FROM
      TableX AS a

#1