SQL 查询列中行的差异

发布于 2024-12-02 12:35:21 字数 825 浏览 2 评论 0原文

我正在努力为以下任务编写查询：

 X   Y                                       X    Y    Seq    Difference 
---------                                  ------------------------------
 20   35                                     20   35   1        35  (45 - 0)
 21   45           ------>                   21   45   1        45 (35-0)
 21   52                                     21   52   2        7 (52-45)
 22   66                                     22   66   1        66 (66-0) 
 22   68                                     22   68   2        2 (68-66)
 22   77                                     22   77   3        9 (77 - 68)

左侧的表给出了 X 列的排序位置。右边的表是我试图用两个额外的列 Seq 和 Difference 生成的表。 Seq 计算 X 中唯一成员的数量并分配一个顺序值（在上面的示例中，有一个 20 、两个 21 和三个 22 ）。差异列获取每个 UNIQUE X 的连续行的差异。

非常感谢您的帮助。

谢谢

原文

I am struggling to write a query for the following task:

 X   Y                                       X    Y    Seq    Difference 
---------                                  ------------------------------
 20   35                                     20   35   1        35  (45 - 0)
 21   45           ------>                   21   45   1        45 (35-0)
 21   52                                     21   52   2        7 (52-45)
 22   66                                     22   66   1        66 (66-0) 
 22   68                                     22   68   2        2 (68-66)
 22   77                                     22   77   3        9 (77 - 68)

The table on the left is given where column X is sorted. The table on the right is what I am trying to generate with two extra clumns Seq and Difference. Seq counts the number of unique members in X and assigns a sequential value (in the example above there are one 20 , two 21s and three 22s) . The difference column takes the differences of consecutive rows of each UNIQUE X.

Your help is much much appreciated.

thanks

分享到QQ

分享到微博

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

发布评论

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

颜漓半夏 2024-12-09 12:35:21

无法通过简单的查询来完成此操作，因为每一行无法访问其他行的内容，但您可以轻松使用游标并使用此数据构建临时表。像这样的东西

DECLARE db_cursor CURSOR FOR  
select x, y from tablename

OPEN db_cursor   
FETCH NEXT FROM db_cursor INTO @x, @y

WHILE @@FETCH_STATUS = 0   
BEGIN   
    -- Do your math and inserts here  
    FETCH NEXT FROM db_cursor INTO @x, @y
END   

CLOSE db_cursor   
DEALLOCATE db_cursor

Can't do this with a simple query as each row cannot access the contents of the others, but you could easily use a cursor and build a temp table with this data. Something like

DECLARE db_cursor CURSOR FOR  
select x, y from tablename

OPEN db_cursor   
FETCH NEXT FROM db_cursor INTO @x, @y

WHILE @@FETCH_STATUS = 0   
BEGIN   
    -- Do your math and inserts here  
    FETCH NEXT FROM db_cursor INTO @x, @y
END   

CLOSE db_cursor   
DEALLOCATE db_cursor

回复收藏 0 原文

╰つ倒转 2024-12-09 12:35:21

您还可以使用子查询。它还在内存中生成一个可在最终结果集中使用的临时表。

回复收藏 0 原文

久随 2024-12-09 12:35:21

像这样的东西应该有效：

create table leftSide
(
X int,
Y int
)
insert into leftSide select 20, 35
insert into leftSide select 21, 45
insert into leftSide select 21, 52
insert into leftSide select 22, 66
insert into leftSide select 22, 68
insert into leftSide select 22, 77

;WITH MyCte (X, Y, Seq)
AS
(
select X,Y,
       ROW_NUMBER() OVER(PARTITION BY X ORDER BY X) AS Seq
from leftSide
)
select a.*, Diff = (a.Y - coalesce(b.Y,0))
from MyCte a
left join MyCte b
on a.X = b.X
    and a.Seq = b.Seq + 1

Something like this should work:

create table leftSide
(
X int,
Y int
)
insert into leftSide select 20, 35
insert into leftSide select 21, 45
insert into leftSide select 21, 52
insert into leftSide select 22, 66
insert into leftSide select 22, 68
insert into leftSide select 22, 77

;WITH MyCte (X, Y, Seq)
AS
(
select X,Y,
       ROW_NUMBER() OVER(PARTITION BY X ORDER BY X) AS Seq
from leftSide
)
select a.*, Diff = (a.Y - coalesce(b.Y,0))
from MyCte a
left join MyCte b
on a.X = b.X
    and a.Seq = b.Seq + 1

回复收藏 0 原文

~没有更多了~