SQLize Online / PHPize Online  /  SQLtest Online

A A A
Share      Blog   Popular
Copy Format Clear
create table Operations ("Operator" text, dDate date, tTime time, sOper text); insert into Operations values ('Иванова', '2020-06-01', '14:29:00', 'Расход'), ('Иванова', '2020-06-01', '14:30:01', 'Расход'), ('Иванова', '2020-06-01', '14:35:17', 'Приход'), ('Иванова', '2020-06-01', '14:30:25', 'Контроль'), ('Петрова', '2020-06-01', '14:35:18', 'Приход'), ('Петрова', '2020-06-01', '14:35:19', 'Приход'), ('Петрова', '2020-06-01', '14:35:20', 'Контроль'); ---------------------------------------------------------------------------------------| select * from Operations; explain with t as ( select *, row_number() over(partition by "Operator", dDate order by tTime) as rn from Operations ) select a."Operator", a.dDate, a.tTime as tOper, b.tTime as tControl from t as a, t as b where a."Operator" = b."Operator" and a.dDate = b.dDate and a.rn = b.rn - 1 and b.sOper = 'Контроль'; ---------------------------------------------------------------------------------------| explain execute 'with t as ( select *, row_number() over(partition by "Operator", dDate order by tTime) as rn from Operations ) select a."Operator", a.dDate, a.tTime as tOper, b.tTime as tControl from t as a, t as b where a."Operator" = b."Operator" and a.dDate = b.dDate and b.sOper = ''' || Контроль
Stuck with a problem? Got Error? Ask ChatGPT!
Copy Clear