SQLize Online / PHPize Online  /  SQLtest Online

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; 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 select "Operator", dDate, tTime, case when sOper = 'Контроль' then tTime else null end -- max(tTime) filter (where sOper = 'Контроль') as tControl from Operations; -- group by 1, 2 select * from Operations; execute 'select * from Operations;'