SQLize Online / PHPize Online  /  SQLtest Online

create table users( id number(10) primary key, C_FIO varchar2(100) ); insert into users(id, C_FIO) values(1, 'Иванов,Иван,Иванович'); insert into users(id, C_FIO) values(2, 'Ярышкин,Валентин,Константинович'); insert into users(id, C_FIO) values(3, 'Кутькина,Алена,Викторовна'); insert into users(id, C_FIO) values(4, 'Пендюр,Валентина,Степановна'); insert into users(id, C_FIO) values(5, 'Кучканова,Алефтина,Игоревна'); insert into users(id, C_FIO) values(6, 'Черных,Петр,Алексеевич'); insert into users(id, C_FIO) values(7, 'Моисеева,Лариса,Михайловна'); insert into users(id, C_FIO) values(8, 'Ласковых,Дмитрий,Викторович'); insert into users(id, C_FIO) values(9, 'Ярынова,Юлия,Павловна'); insert into users(id, C_FIO) values(10, 'Кулиш,Маргарита,Евгеньевна'); select*from users; create table address( id number(10) primary key, --идентификатор адреса прописки c_address varchar2(4000) ); insert into address(id, c_address) values (1, 'г.Сызрань, ул.Ильича , 5, 7, 303030'); insert into address(id, c_address) values (2, 'г.Сызрань, Свободы , 45, 17, 303450'); insert into address(id, c_address) values (3, 'г.Самара, ул.Владимирская , 6, 78, 123030'); insert into address(id, c_address) values (4, 'г.Самара, ул.Пензинская , 190, 76, 303190'); insert into address(id, c_address) values (5, 'г.Самара, ул.Гагарина , 15, 1, 303115'); insert into address(id, c_address) values (6, 'г.Самара, ул.Ленинградская , 4, 1, 303190'); insert into address(id, c_address) values (7, 'г.Самара, ул.Димитрова , 88, 99, 303199'); insert into address(id, c_address) values (8, 'г.Самара, ул.Мориса Тореза , 17, 56, 303117'); insert into address(id, c_address) values (9, 'г.Самара, ул.Высоцского , 3, 21, 303250'); insert into address(id, c_address) values (10, 'г.Самара, ул.Мяги , 13, 76, 303190'); select*from address; CREATE TABLE user_on_address ( ID NUMBER(10)PRIMARY KEY, c_user NUMBER NOT NULL REFERENCES USERS(ID), c_address NUMBER NOT NULL REFERENCES address(ID), c_begin DATE NOT NULL, c_end DATE ); INSERT into user_on_address (ID, C_USER, C_ADDRESS, C_BEGIN, C_END) values (1, 1, 1, to_date('01-01-1978', 'dd-mm-yyyy'), null); insert into user_on_address (ID, C_USER, C_ADDRESS, C_BEGIN, C_END) values (2, 1, 2, to_date('12-01-2000', 'dd-mm-yyyy'), null); insert into user_on_address (ID, C_USER, C_ADDRESS, C_BEGIN, C_END) values (3, 3, 3, to_date('03-01-1980', 'dd-mm-yyyy'), null); insert into user_on_address (ID, C_USER, C_ADDRESS, C_BEGIN, C_END) values (4, 4, 4, to_date('08-12-1973', 'dd-mm-yyyy'), to_date('08-12-1999', 'dd-mm-yyyy')); insert into user_on_address (ID, C_USER, C_ADDRESS, C_BEGIN, C_END) values (5, 5, 5, to_date('05-01-2010', 'dd-mm-yyyy'), null); insert into user_on_address (ID, C_USER, C_ADDRESS, C_BEGIN, C_END) values (6, 6, 6, to_date('08-01-2013', 'dd-mm-yyyy'), to_date('17-06-2015', 'dd-mm-yyyy')); insert into user_on_address (ID, C_USER, C_ADDRESS, C_BEGIN, C_END) values (7, 7, 7, to_date('01-02-2011', 'dd-mm-yyyy'), null); insert into user_on_address (ID, C_USER, C_ADDRESS, C_BEGIN, C_END) values (8, 8, 8, to_date('14-01-2000', 'dd-mm-yyyy'), to_date('14-03-2005', 'dd-mm-yyyy')); insert into user_on_address (ID, C_USER, C_ADDRESS, C_BEGIN, C_END) values (9, 9, 9, to_date('15-01-2003', 'dd-mm-yyyy'), null); insert into user_on_address (ID, C_USER, C_ADDRESS, C_BEGIN, C_END) values (10, 9, 10, to_date('10-07-2005', 'dd-mm-yyyy'), null); select uoa.C_USER, uoa.C_BEGIN, uoa.C_END,1 AS p_mode from user_on_address uoa join users u1 on uoa.id = u1.id -- 1 условие p_mode =1 where uoa.C_END is null and uoa.C_BEGIN <=current_date and uoa.C_BEGIN = (select max(uo.C_BEGIN) from user_on_address uo join users u on uo.id =u.id where uo.c_user=uoa.c_user ) -- 2 условие UNION ALL (select uoa.C_USER,uoa.C_BEGIN, uoa.C_END,0 AS p_mode from user_on_address uoa join users u1 on uoa.id = u1.id) UNION ALL (select uoa.C_USER,uoa.C_BEGIN, uoa.C_END,-1 AS p_mode from user_on_address uoa join users u1 on uoa.id = u1.id and uoa.C_END is not null ) WITH a AS (SELECT u.id,u.c_fio,a.c_address,SUBSTR(a.c_address,-6)zipcode, ua.id id_address,ua.c_begin,ua.c_end FROM USERS u INNER JOIN user_on_address ua ON (u.id = ua.c_user) INNER JOIN address a ON (a.id=ua.c_address)) SELECT a3.c_fio,a3.c_address,a3.zipcode FROM (SELECT a.c_fio,a.c_address,a.zipcode,0 AS p_mode FROM a UNION ALL SELECT a.c_fio,a.c_address,a.zipcode,1 AS FROM a WHERE a.id_address IN (SELECT MAX(id_address) FROM a a2 WHERE a.id = a2.id AND a2.c_end IS NULL) UNION ALL SELECT a.c_fio,a.c_address,a.zipcode,-1 AS FROM a WHERE a.id_address IN (SELECT MAX(id_address) FROM a a2 WHERE a.id = a2.id AND a2.c_end IS NOT NULL))a3 WHERE a3.p_mode =&pmode