SQL Server 查询连续两条记录 thisqty=1 的时间差

使用 LAG 函数来获取前一行的值，然后通过判断前一行和当前行的 'thisqty' 是否都为 1，来筛选出符合条件的记录。最后使用 DATEDIFF 函数计算时间差。

示例代码如下：

SELECT 
    *,
    DATEDIFF(second, prev_time, TIME_STAMP) AS time_diff
FROM (
    SELECT 
        *,
        LAG(thisqty) OVER (ORDER BY TIME_STAMP) AS prev_qty,
        LAG(TIME_STAMP) OVER (ORDER BY TIME_STAMP) AS prev_time
    FROM user
) AS t
WHERE 
    thisqty = 1 AND prev_qty = 1
ORDER BY TIME_STAMP