查詢以根據主鍵的布爾值順序從同一列獲取開始時間和結束時間

截至目前，這是我目前的查詢：

SELECT
   MAX(CASE WHEN status = 1 THEN [timestamp] END) as machineOn
   ,MAX(CASE WHEN status = 0 THEN [timestamp] END) as machineOff

FROM machine_table
where machineno='mc202101-123'
group by machineno, timestamp order by timestamp desc

您可以單獨使用視窗函式來完成此操作，無需分組。

WITH NextValue AS
(
   SELECT *,
     LEAD(status, 1, -1) OVER (ORDER BY [timestamp]) AS NextStatus
   FROM machine_table
   WHERE machineno = 'mc202101-123'
),
Filtered AS (
   SELECT *,
     LEAD([timestamp]) OVER (ORDER BY [timestamp]) AS NextTimestamp 
   FROM NextValue
   WHERE NextStatus <> Status
)
SELECT
 [timestamp] AS machineOn,
 NextTimestamp AS machineOff
FROM Filtered
WHERE Status = 1
ORDER BY
 timestamp DESC;

如果您想為每個執行此操作，machineno則將每個OVER子句更改為

OVER (PARTITION BY machineno ORDER BY [timestamp])

引用自：https://dba.stackexchange.com/questions/300522