sql中同一个表的上下两行之间的某个字段相减有关问题

https://blog.csdn.net/cassiel33/article/details/9187767

https://bbs.csdn.net/topics/390908213

select a.id,a.数量 - b.数量
from abc a left join abc b on a.id + 1 = b.id

 

select
   datediff(mi,b.start_time ,a.end_time)
from
   (select id=row_number()over(order by getdate()),* from tb)a,
   (select id=row_number()over(order by getdate()),* from tb)b
where
   a.id=b.id-1

 

SELECT a.RowId,
       b.RowId,
       a.CreateOn,
       b.CreateOn,
       DATEDIFF(SECOND, b.CreateOn, a.CreateOn)
FROM
(
    SELECT ROW_NUMBER() OVER (ORDER BY a.CreateOn ASC) AS RowId,
           a.CreateOn
    FROM dbo.tbm_tpl_TaskProcessorLog a
        INNER JOIN dbo.tbm_sjb_ScheduledJob AS b
            ON a.TaskID = b.ScheduledJobID
    WHERE a.TaskID = 1109
          AND a.CreateOn >= '20200317'
) AS a
    INNER JOIN
    (
        SELECT ROW_NUMBER() OVER (ORDER BY a.CreateOn ASC) AS RowId,
               a.CreateOn
        FROM dbo.tbm_tpl_TaskProcessorLog a
            INNER JOIN dbo.tbm_sjb_ScheduledJob AS b
                ON a.TaskID = b.ScheduledJobID
        WHERE a.TaskID = 1109
              AND a.CreateOn >= '20200317'
    ) AS b
        ON a.RowId - 1 = b.RowId;

 

 

;WITH t
AS (SELECT ROW_NUMBER() OVER (ORDER BY a.CreateOn ASC) AS RowId,
           a.CreateOn
    FROM dbo.tbm_tpl_TaskProcessorLog a
        INNER JOIN dbo.tbm_sjb_ScheduledJob AS b
            ON a.TaskID = b.ScheduledJobID
    WHERE a.TaskID = 1109
          AND a.CreateOn >= '20200317')
SELECT a.*,
       b.CreateOn AS BCreatedOn,
       val = DATEDIFF(SECOND, a.CreateOn, b.CreateOn)
FROM t a
    LEFT JOIN t AS b
        ON a.RowId + 1 = b.RowId;

 

 

 

作者:Chuck Lu    GitHub