寻求修复小型sql查询的帮助

Question

我需要一个查询来返回过去21天的时间条目列表，按成员id和开始时间对该列表进行排序，然后将每行的开始时间与前一行的结束时间进行比较，然后显示开始时间大于前一行结束时间的行。

不幸的是，我对sql的所有知识都不太了解，到目前为止我的查询返回了一个错误，我不知道我需要更改/添加/删除什么来修复它。

下面是我开始使用的视图的一个示例(想象一下还有几千行(。起始视图

当我运行以下查询时，我会按照我想要的方式排列数据。

SELECT
t.time_recID AS id
,t.time_recID AS time_recid
,LOWER(t.Member_ID) AS member_id
,CAST(t.Time_Start_UTC AS DATETIME) AS evt_start
,CAST(t.Time_End_UTC AS DATETIME) AS evt_end
,LAG(t.Time_End_UTC,1,0) OVER (PARTITION BY t.Member_ID ORDER BY t.Time_Start_UTC) AS prev_evtend
FROM v_evt_time AS t
WHERE DATEADD (Day, -21, CURRENT_TIMESTAMP) <= t.Date_Start
ORDER BY member_id, evt_start
OFFSET 0 ROWS

查询结果接近

但是，当我在WHERE子句中添加一行来比较evt_start和prev_evtend列时，如下所示…

WHERE DATEADD (Day, -21, CURRENT_TIMESTAMP) <= t.Date_Start
AND evt_start < prev_evtend

我收到一个错误，上面写着"无效列名"evt_start"。列名"prev_evtstart"无效。

我需要做些什么来修复查询以返回我需要的结果？我们非常感谢您的帮助，并提前表示感谢。

Answer 1

列名evt_start和prev_evtend在定义它们的select语句中不可用。

您可以使用以下通用表表达式：

With entries as (
SELECT
t.time_recID AS id
,t.time_recID AS time_recid
,LOWER(t.Member_ID) AS member_id
,CAST(t.Time_Start_UTC AS DATETIME) AS evt_start
,CAST(t.Time_End_UTC AS DATETIME) AS evt_end
,LAG(t.Time_End_UTC,1,0) OVER (PARTITION BY t.Member_ID ORDER BY t.Time_Start_UTC) AS prev_evtend
FROM v_evt_time AS t
WHERE DATEADD (Day, -21, CURRENT_TIMESTAMP) <= t.Date_Start
ORDER BY member_id, evt_start
OFFSET 0 ROWS)
select * 
from entries
WHERE DATEADD (Day, -21, CURRENT_TIMESTAMP) <= t.Date_Start
AND evt_start < prev_evtend

这在功能上相当于在原始查询之上的查询：

select *
from (
SELECT
t.time_recID AS id
,t.time_recID AS time_recid
,LOWER(t.Member_ID) AS member_id
,CAST(t.Time_Start_UTC AS DATETIME) AS evt_start
,CAST(t.Time_End_UTC AS DATETIME) AS evt_end
,LAG(t.Time_End_UTC,1,0) OVER (PARTITION BY t.Member_ID ORDER BY t.Time_Start_UTC) AS prev_evtend
FROM v_evt_time AS t
WHERE DATEADD (Day, -21, CURRENT_TIMESTAMP) <= t.Date_Start
ORDER BY member_id, evt_start
OFFSET 0 ROWS) sub
WHERE DATEADD (Day, -21, CURRENT_TIMESTAMP) <= t.Date_Start
AND evt_start < prev_evtend