SQL列中的增量日期值

Question

有一个SQL查询:

DECLARE sptv cursor local fast_forward for
SELECT GETDATE() as Time,
r.session_id,
r.cpu_time,
p.physical_io,
t.text,
substring(t.text, r.statement_start_offset/2 + 1, case when r.statement_end_offset = -1 then len(t.text) else (r.statement_end_offset - r.statement_start_offset)/2 end) as text_running,
p.blocked,
db_name(p.dbid) as dbname,
r.status,
r.command,
r.start_time,
r.wait_type,
p.waitresource,
p.status,
p.open_tran,
p.loginame,         
p.hostname,
p.program_name,
r.percent_complete,
r.wait_type,
r.last_wait_type,
p.waittime
from sys.dm_exec_requests r
cross apply sys.dm_exec_sql_text(r.sql_handle) t
inner join sys.sysprocesses p on p.spid = r.session_id
open sptv;
while 1 = 1
begin
fetch next from sptv;
if @@fetch_status <> 0
break;
end;
close sptv;
deallocate sptv;

我希望time列中的时间每行增加1秒。我设法用下面的查询做到了:

CREATE TABLE #ActibeUser
(
Time DATETIME,
ID int identity(1,1),
session_id INT,
cpu_time INT,
physical_io INT,
text VARCHAR(MAX),
text_running VARCHAR(MAX),
blocked INT,
dbname VARCHAR(MAX),
status1 VARCHAR(MAX),
command VARCHAR(MAX),
start_time DATETIME,
wait_type1 VARCHAR(MAX),
waitresource VARCHAR(MAX),
status2 VARCHAR(MAX),
open_tran INT,
loginame VARCHAR(MAX),
hostname VARCHAR(MAX),
program_name VARCHAR(MAX),
percent_complete INT,
wait_type2 VARCHAR(MAX),
last_wait_type VARCHAR(MAX),
waittime INT
)
INSERT INTO #ActibeUser
(
Time,
session_id,
cpu_time,
physical_io,
text,
text_running,
blocked,
dbname,
status1,
command,
start_time,
wait_type1,
waitresource,
status2,
open_tran,
loginame,
hostname,
program_name,
percent_complete,
wait_type2,
last_wait_type,
waittime
)
SELECT GETDATE() AS Time,
r.session_id,
r.cpu_time,
p.physical_io,
t.text,
substring(t.text, r.statement_start_offset/2 + 1, case when r.statement_end_offset = -1 then len(t.text) else (r.statement_end_offset - r.statement_start_offset)/2 end) as text_running,
p.blocked,
db_name(p.dbid) as dbname,
r.status,
r.command,
r.start_time,
r.wait_type,
p.waitresource,
p.status,
p.open_tran,
p.loginame,         
p.hostname,
p.program_name,
r.percent_complete,
r.wait_type,
r.last_wait_type,
p.waittime
from sys.dm_exec_requests r
cross apply sys.dm_exec_sql_text(r.sql_handle) t
inner join sys.sysprocesses p on p.spid = r.session_id
DECLARE sptv cursor local fast_forward for
SELECT * FROM #ActibeUser
open sptv;
while 1 = 1
begin
fetch next from sptv;
if @@fetch_status <> 0
break;
UPDATE #ActibeUser
SET Time = DATEADD(SS,1,Time)
end;
close sptv;
deallocate sptv;
DROP TABLE #ActibeUser

但是它不适合我，我不能使用临时表。请帮忙纠正第一个请求。我将接收到的数据传输到Zabbix，并从它传输到Grafana。

Answer 1

您可以在SELECT中使用DATEADD和ROW_NUMBER。

你也不应该使用sys.sysprocesses，它已被弃用。用sys.dm_exec_sessions代替。

SELECT DATEADD(second, ROW_NUMBER() OVER (ORDER BY (SELECT NULL)), GETDATE()) AS Time,
r.session_id,
r.cpu_time,
physical_io = s.reads + s.writes,
t.text,
substring(t.text, r.statement_start_offset/2 + 1, case when r.statement_end_offset = -1 then len(t.text) else (r.statement_end_offset - r.statement_start_offset)/2 end) as text_running,
r.blocking_session_id,
db_name(s.database_id) as dbname,
r.status,
r.command,
r.start_time,
r.wait_type,
r.waitresource,
s.status,
s.open_transaction_count,
s.login_name,         
s.host_name,
s.program_name,
r.percent_complete,
r.wait_type,
r.last_wait_type,
r.waittime
from sys.dm_exec_requests r
inner join sys.dm_exec_sessions s on s.session_id = r.session_id
cross apply sys.dm_exec_sql_text(r.sql_handle) t