tsql - 使用 cte 和变量来更新 SQL 中的日期

Question

我目前正在尝试实现一些代码，它将获取给定 160 行行列表的日期，并将它们更新为同一表中另一个日期后一分钟。

所以例如从这些将来自：

> 1058841   2018-06-20 14:15:04.000 Copy of NtO produced
> 1058841   2018-06-14 19:58:03.000 NTO service date set to 24/05/2018

> 969565    2018-06-20 14:15:01.000 17530   Copy of NtO produced
> 969565    2018-06-14 19:58:03.000 148 NTO service date set to 24/05/2018

对此：

> 1058841   2018-06-14 19:59:03.000 Copy of NtO produced
> 1058841   2018-06-14 19:58:03.000 NTO service date set to 24/05/2018

> 969565    2018-06-14 19:59:03.000 17530   Copy of NtO produced
> 969565    2018-06-14 19:58:03.000 148 NTO service date set to 24/05/2018

我到目前为止的代码：

declare @thisdate datetime

set @thisdate = (
select * from (
select row_number() over (partition by te_system_ref order by (select 0)) as rownumber, te_date, te_system_ref, te_event from ticket_events where te_system_ref in 
(select sl_system_ref from statutory_letter where sl_letter_batch = 9429)and te_event = 'Copy of NtO produced'
) t
where rownumber = 1
);

with rn as 
( select * from (
select row_number() over (partition by te_system_ref order by (select 0)) as rownumber1, te_date, te_system_ref, te_event from ticket_events where te_system_ref in 
(
select sl_system_ref from statutory_letter where sl_letter_batch = 9429 
)
and te_event = 'Copy of NtO produced'
) t where rownumber1 = 1)

select * from rn where te_date between dateadd(hour,1,@thisdate) and dateadd(hour,-1,@thisdate)

这目前给了我错误：

Msg 116, Level 16, State 1, Line 9 当子查询没有用 EXISTS 引入时，只能在选择列表中指定一个表达式。

如果有人能解释我哪里出错了，或者指出我正确的方向，我将不胜感激

标签： tsqlvariablescommon-table-expressionrow-number

Answer 1

set @thisdate = (
select * from (
select row_number() over (partition by te_system_ref order by (select 0)) as rownumber, te_date, te_system_ref, te_event from ticket_events where te_system_ref in 
(select sl_system_ref from statutory_letter where sl_letter_batch = 9429)and te_event = 'Copy of NtO produced'
) t
where rownumber = 1
);

在这里，您尝试将几个值分配给一个标量变量。

我猜你实际上想要te_date.

set @thisdate = (
select te_date from (
select row_number() over (partition by te_system_ref order by (select 0)) as rownumber, te_date from ticket_events where te_system_ref in 
(select sl_system_ref from statutory_letter where sl_letter_batch = 9429)and te_event = 'Copy of NtO produced'
) t
where rownumber = 1
);