关于#SQLSERVER#的问题，如何解决？

Question

如何用SQL Server 查询下面数据State 为1的时间间隔
4-10行间隔时间，19-23行间隔时间，27-33行间隔时间

NO State DATETIME
1 0 5/31/24 13:30
2 0 5/31/24 13:31
3 0 5/31/24 13:32
4 1 5/31/24 13:33
5 1 5/31/24 13:34
6 1 5/31/24 13:35
7 1 5/31/24 13:36
8 1 5/31/24 13:37
9 1 5/31/24 13:40
10 1 5/31/24 13:41
11 0 5/31/24 13:42
12 0 5/31/24 13:43
13 0 5/31/24 13:44
14 0 5/31/24 13:45
15 0 5/31/24 13:46
16 0 5/31/24 13:47
17 0 5/31/24 13:48
18 0 5/31/24 13:49
19 1 5/31/24 13:50
20 1 5/31/24 13:51
21 1 5/31/24 13:52
22 1 5/31/24 13:53
23 1 5/31/24 13:54
24 0 5/31/24 13:55
25 0 5/31/24 13:56
26 0 5/31/24 13:57
27 1 5/31/24 13:58
28 1 5/31/24 13:59
29 1 5/31/24 14:00
30 1 5/31/24 14:01
31 1 5/31/24 14:02
32 1 5/31/24 14:03
33 1 5/31/24 14:04
34 0 5/31/24 14:05
35 0 5/31/24 14:06
36 0 5/31/24 14:07
37 1 5/31/24 14:08
38 1 5/31/24 14:09

Answer 1

以下内容由CHATGPT及阿里嘎多学长共同生成、有用望采纳：

---

要解决这个问题，我们可以使用SQL Server的窗口函数（如LEAD和LAG）来查找State为1的每个连续区间的开始和结束时间，然后计算这些区间的持续时间。以下是实现这个查询的步骤和示例SQL代码：

1. 使用LEAD函数来获取当前行的下一行的State值。
2. 使用LAG函数来获取当前行的上一行的State值。
3. 通过比较当前行的State值和上一行以及下一行的State值，来确定State为1的区间的开始和结束。
4. 使用DATEDIFF函数来计算每个区间的持续时间。

以下是SQL查询的示例代码：

WITH CTE AS (
    SELECT 
        NO,
        State,
        DATETIME,
        LEAD(State, 1, 0) OVER (ORDER BY NO) AS NextState,
        LAG(State, 1, 0) OVER (ORDER BY NO) AS PrevState
    FROM YourTableName
    WHERE State = 1
)
, Intervals AS (
    SELECT 
        NO,
        DATETIME,
        CASE 
            WHEN PrevState = 0 OR NO = 1 THEN 1 -- 开始标记
            ELSE 0 
        END AS StartFlag,
        CASE 
            WHEN NextState = 0 THEN 1 -- 结束标记
            ELSE 0 
        END AS EndFlag
    FROM CTE
)
, MarkedIntervals AS (
    SELECT 
        NO,
        DATETIME,
        StartFlag,
        EndFlag,
        ROW_NUMBER() OVER (ORDER BY NO) AS RowNum
    FROM Intervals
)
, RunningTotal AS (
    SELECT 
        NO,
        DATETIME,
        StartFlag,
        EndFlag,
        SUM(StartFlag) OVER (ORDER BY NO) AS RunningStart,
        SUM(EndFlag) OVER (ORDER BY NO) AS RunningEnd
    FROM MarkedIntervals
)
SELECT 
    NO,
    DATETIME AS StartTime,
    DATEADD(MINUTE, (RunningStart - RunningEnd), DATIME) AS EndTime,
    DATEDIFF(MINUTE, DATIME, DATEADD(MINUTE, (RunningStart - RunningEnd), DATIME)) AS DurationMinutes
FROM RunningTotal
WHERE RunningStart > RunningEnd -- 确保只选择区间的开始行
ORDER BY NO;

请注意，你需要将YourTableName替换为你的实际表名。

这个查询首先创建了一个CTE（公用表表达式），它包含了每行的State值以及它的前一行和后一行的State值。然后，我们标记了每个State为1区间的开始和结束。接下来，我们使用RunningTotal来计算每个区间的持续时间。最后，我们选择了每个区间的开始时间和持续时间。

至于参考资料，你可以查看以下链接来了解更多关于SQL Server窗口函数和DATEDIFF函数的信息：

• SQL Server 窗口函数
• DATEDIFF (Transact-SQL)

希望这能帮助你解决问题！如果你有任何疑问或需要进一步的帮助，请随时告诉我。