SQLize
Online
/
PHPize Online
/
SQLtest Online
A
A
A
Share
Donate
Blog
Popular
Donate
A
A
A
Share
Blog
Popular
SQLize.online is a free online SQL environment for quickly running, experimenting with and sharing code.
You can run your SQL code on top of the most popular RDBMS including MySQL, MariaDB, SQLite, PostgreSQL, Oracle and Microsoft SQL Server.
SQL code:
Upload
Copy
Format
Clear
-- SCHEMA create table location_dict(id number, name varchar2(200)); create table unit_dict(unit_id number not null, location_id number, parent_id number, name varchar2(200), parameter_count number); create table pipes(matid number, pipe_no varchar2(200)); create table unit_passes(pass_id number, matid number, parent_pass_id number, unitid number, dt date, duration number); comment on column location_dict.id is 'Идентификатор участка'; comment on column location_dict.name is 'Наименование'; comment on column unit_dict.unit_id is 'Идентификатор агрегата'; comment on column unit_dict.location_id is 'Идентификатор участка'; comment on column unit_dict.parent_id is 'Идентификатор группового агрегата'; comment on column unit_dict.name is 'Наименование'; comment on column unit_dict.parameter_count is 'Количество параметров у агрегата'; comment on column pipes.matid is 'Идентификатор'; comment on column pipes.pipe_no is 'Номер трубы'; comment on column unit_passes.pass_id is 'Номер прохода'; comment on column unit_passes.matid is 'Идентифкатор продукции'; comment on column unit_passes.parent_pass_id is 'Идентификатор предыдущего прохода'; comment on column unit_passes.unitid is 'Агрегат'; comment on column unit_passes.dt is 'Дата обработки'; comment on column unit_passes.duration is 'Продолжительность, с'; alter table location_dict add constraint pk_location_dict primary key (id); alter table unit_dict add constraint pk_unit_dict primary key (unit_id); alter table unit_dict add constraint fk_location_dict foreign key (location_id) references location_dict (id) on delete cascade; alter table unit_dict add constraint fk_unit_dict foreign key (parent_id) references unit_dict (unit_id) on delete cascade; alter table pipes add constraint pk_pipes primary key (matid); alter table unit_passes add constraint pk_unit_passes primary key (pass_id); alter table unit_passes add constraint fk_unit_passes foreign key (parent_pass_id) references unit_passes (pass_id) on delete cascade; alter table unit_passes add constraint dk_pipes foreign key (matid) references pipes (matid) on delete cascade; commit; -- SQL delete from unit_dict; delete from location_dict; commit; insert into location_dict (id, name) values (1, 'Участок 1'); insert into location_dict (id, name) values (2, 'Участок 2'); insert into location_dict (id, name) values (3, 'Участок 3'); commit; insert into unit_dict (unit_id, location_id, parent_id, name, parameter_count) values (1, 1, null, 'ТЭСЦ', 100); insert into unit_dict (unit_id, location_id, parent_id, name, parameter_count) values (2, 2, null, 'ЦПиОП', 1000); insert into unit_dict (unit_id, location_id, parent_id, name, parameter_count) values (3, 1, 1, 'ТФУ', 10); insert into unit_dict (unit_id, location_id, parent_id, name, parameter_count) values (4, 1, 1, 'ВСС', 2000); insert into unit_dict (unit_id, location_id, parent_id, name, parameter_count) values (5, 1, 1, 'НСС', 1100); insert into unit_dict (unit_id, location_id, parent_id, name, parameter_count) values (6, 2, 2, 'ОНП', 100); insert into unit_dict (unit_id, location_id, parent_id, name, parameter_count) values (7, 2, 2, 'ОВП', 50); insert into unit_dict (unit_id, location_id, parent_id, name, parameter_count) values (8, 1, 3, 'КГП', 10); insert into unit_dict (unit_id, location_id, parent_id, name, parameter_count) values (9, 1, 3, 'КФУ', 10); insert into unit_dict (unit_id, location_id, parent_id, name, parameter_count) values (10, 1, 4, 'ВСС1', 135); insert into unit_dict (unit_id, location_id, parent_id, name, parameter_count) values (11, 1, 4, 'ВСС2', 135); insert into unit_dict (unit_id, location_id, parent_id, name, parameter_count) values (12, 1, 4, 'ВСС3', 140); insert into unit_dict (unit_id, location_id, parent_id, name, parameter_count) values (13, 1, 5, 'НСС1', 160); insert into unit_dict (unit_id, location_id, parent_id, name, parameter_count) values (14, 1, 5, 'НСС2', 160); insert into unit_dict (unit_id, location_id, parent_id, name, parameter_count) values (15, 1, 5, 'НСС3', 160); insert into unit_dict (unit_id, location_id, parent_id, name, parameter_count) values (16, 2, 6, 'Экструдер', 12); insert into unit_dict (unit_id, location_id, parent_id, name, parameter_count) values (17, 2, 7, 'Покраска', 28); insert into unit_dict (unit_id, location_id, parent_id, name, parameter_count) values (18, 2, 7, 'Мойка', 5); insert into unit_dict (unit_id, location_id, parent_id, name, parameter_count) values (19, 2, 7, 'Покраска', 5); insert into unit_dict (unit_id, location_id, parent_id, name, parameter_count) values (21, 3, null, 'Приемка', 0); commit; delete from pipes; delete from unit_passes; commit; insert into pipes (matid, pipe_no) values (123456, 'Итз125704.9'); insert into pipes (matid, pipe_no) values (123455, '125703.7_итз'); commit; insert into unit_passes (pass_id, matid, parent_pass_id, unitid, dt, duration) values (1, 123456, null, 1, to_date('01.04.2016', 'dd.mm.yyyy'), 10); insert into unit_passes (pass_id, matid, parent_pass_id, unitid, dt, duration) values (2, 123456, 1, 2, to_date('01.04.2016', 'dd.mm.yyyy'), 20); insert into unit_passes (pass_id, matid, parent_pass_id, unitid, dt, duration) values (3, 123455, null, 1, to_date('01.05.2016', 'dd.mm.yyyy'), 30); insert into unit_passes (pass_id, matid, parent_pass_id, unitid, dt, duration) values (4, 123456, 2, 5, to_date('01.05.2016', 'dd.mm.yyyy'), 40); insert into unit_passes (pass_id, matid, parent_pass_id, unitid, dt, duration) values (5, 123456, 4, 5, to_date('02.05.2016', 'dd.mm.yyyy'), 40); insert into unit_passes (pass_id, matid, parent_pass_id, unitid, dt, duration) values (6, 123456, 5, 11, to_date('03.05.2016', 'dd.mm.yyyy'), 40); insert into unit_passes (pass_id, matid, parent_pass_id, unitid, dt, duration) values (7, 123455, 3, 22, to_date('04.05.2016', 'dd.mm.yyyy'), 30); insert into unit_passes (pass_id, matid, parent_pass_id, unitid, dt, duration) values (8, 123455, 7, 22, to_date('05.05.2016', 'dd.mm.yyyy'), 60); insert into unit_passes (pass_id, matid, parent_pass_id, unitid, dt, duration) values (9, 123456, 6, 2, to_date('06.05.2016', 'dd.mm.yyyy'), 10); insert into unit_passes (pass_id, matid, parent_pass_id, unitid, dt, duration) values (10, 123456, 9, 805, to_date('07.05.2016', 'dd.mm.yyyy'), 10); insert into unit_passes (pass_id, matid, parent_pass_id, unitid, dt, duration) values (11, 123455, 8, 5, to_date('07.05.2016', 'dd.mm.yyyy'), 10); insert into unit_passes (pass_id, matid, parent_pass_id, unitid, dt, duration) values (12, 123456, 10, 1, to_date('08.05.2016', 'dd.mm.yyyy'), 10); insert into unit_passes (pass_id, matid, parent_pass_id, unitid, dt, duration) values (13, 123455, 11, 7, to_date('09.05.2016', 'dd.mm.yyyy'), 10); insert into unit_passes (pass_id, matid, parent_pass_id, unitid, dt, duration) values (14, 123456, 12, 7, to_date('12.05.2016', 'dd.mm.yyyy'), 10); insert into unit_passes (pass_id, matid, parent_pass_id, unitid, dt, duration) values (15, 123456, 14, 11, to_date('14.05.2016', 'dd.mm.yyyy'), 100); insert into unit_passes (pass_id, matid, parent_pass_id, unitid, dt, duration) values (16, 123455, 13, 3, to_date('16.05.2016', 'dd.mm.yyyy'), 20); insert into unit_passes (pass_id, matid, parent_pass_id, unitid, dt, duration) values (17, 123455, 16, 21, to_date('18.05.2016', 'dd.mm.yyyy'), 50); insert into unit_passes (pass_id, matid, parent_pass_id, unitid, dt, duration) values (18, 123455, 17, 23, to_date('20.05.2016', 'dd.mm.yyyy'), 60); insert into unit_passes (pass_id, matid, parent_pass_id, unitid, dt, duration) values (19, 123456, 15, 103, to_date('21.05.2016', 'dd.mm.yyyy'), 60); insert into unit_passes (pass_id, matid, parent_pass_id, unitid, dt, duration) values (20, 123456, 19, 104, to_date('22.05.2016', 'dd.mm.yyyy'), 60); commit; WITH cte_prev_pass AS ( SELECT p.MATID, p.DT AS PREV_DT, p.DURATION AS PREV_DURATION FROM UNIT_PASSES p WHERE p.PASS_ID < up.PASS_ID ORDER BY p.MATID, p.DT DESC FETCH FIRST ONLY 1 ROWS OVER BY MATID ) SELECT p.PIPE_NO, up.DT, up.DURATION, cp.PREV_DURATION FROM UNIT_PASSES up JOIN PIPES p ON p.MATID = up.MATID LEFT JOIN cte_prev_pass cp ON cp.MATID = p.MATID WHERE UPPER(p.PIPE_NO) LIKE 'ИТЗ%' ORDER BY up.DT;
SQL
Server:
MariaDB 11.4
MariaDB 11.5
MariaDB 10
MariaDB 10 Sakila (ReadOnly)
MySQL 5.7
MySQL 5.7 Sakila (ReadOnly)
MySQL 8.0
MySQL 8.0 Sakila (ReadOnly)
SQLite 3
PostgreSQL 10 Bookings (ReadOnly)
PostgreSQL 11
PostgreSQL 12
PostgreSQL 13
PostgreSQL 14
PostgreSQL 15
MS SQL Server 2017
MS SQL Server 2019
MS SQL Server 2022
MS SQL Server 2022 AdventureWorks (ReadOnly)
Firebird 4.0
Firebird 4.0 (Employee)
Oracle Database 19c (HR)
Oracle Database 21c
Oracle Database 23c Free
SOQOL
Version
ER Diagram
Preserve result
Stuck with a problem?
Got Error?
Ask ChatGPT!
Result:
Copy
Clear