How to insert data with which to compare the data in the table-CodePudding

If there is now a hotel management forms, how to make the same room number in insert to ensure that in the room number of the latest LiDian after?
For example:

The CREATE TABLE JiuDian
(fangJian INTEGER NOT NULL,
Daoda DATE NOT NULL,
LiDian DATE NOT NULL,
LvKe CHAR (30) NOT NULL,
PRIMARY KEY (fangJian, Daoda));

INSERT INTO JiuDian (fangJian, Daoda LiDian, LvKe)
VALUES
(123, to_date (' 20160202 ', 'YYYYMMDD'), to_date (' 20160206 ', 'YYYYMMDD'), 'A'),
(123, to_date (' 20160204 ', 'YYYYMMDD'), to_date (' 20160208 ', 'YYYYMMDD'), 'B')
;

Equivalent to 123 room was the new passenger check-in before departure,

How to change tables

CodePudding user response:

Insert before find out the current largest LiDian time room, insert the new data for the biggest LiDian DAODA time amended time (or maximum departure time plus one)
Use a trigger or stored procedure

CodePudding user response:

reference 1st floor dd215130268 response:

insert before find out the current largest LiDian time room, insert the new data for the biggest LiDian DAODA time amended time (or maximum departure time plus one)
Use a trigger or stored procedure

Trouble on this, the problem is not allowed to use variables and trigger,

But allowed to change form structure, or increase the redundant table can be (efficiency is not considered)

I have been thinking about it can use the check constraint, later found the check constraint may not be included in the subquery or function, so stuck in it

CodePudding user response:

Insert money, according to the room number first to list check not to go

CodePudding user response:

Write a stored procedure to determine, consider what you said not to variables, then straight view to create two tables

 create table JiuDian as 
(select '101', fangJian to_date (' 20170101 ', 'yyyymmdd') daoda, to_date (' 20170103 ', 'yyyymmdd') likai, 'A' lvke from dual 
Union all select '101', to_date (' 20170104 ', 'yyyymmdd'), to_date (' 20170106 ', 'yyyymmdd'), 'B' from dual 
Union all select '123', to_date (' 20170104 ', 'yyyymmdd'), to_date (' 20170110 ', 'yyyymmdd'), 'C' from dual); 

The create table JiuDianNew as 
(select '101', fangJian to_date (' 20170102 ', 'yyyymmdd') daoda, to_date (' 20170103 ', 'yyyymmdd') likai, 'P' lvke from dual 
Union all select '101', to_date (' 20170107 ', 'yyyymmdd'), to_date (' 20170202 ', 'yyyymmdd'), 'Q' from dual); 

Insert into JiuDian 
Select * from j. (select fangjian, Max (daoda) daoda, Max (likai) likai from JiuDian group by fangjian) t, JiuDianNew j 
Where t.f angJian=j.f angJian 
And t.l ikaicommit; 

Select * from JiuDian. 
Drop table JiuDian; 
Drop table JiuDianNew;

I this is an example, for reference!

CodePudding user response:

reference 4 floor POM_24 response:

write a stored procedure to judge it is good, considering you said not to variables, then straight view to create two tables

 create table JiuDian as 
(select '101', fangJian to_date (' 20170101 ', 'yyyymmdd') daoda, to_date (' 20170103 ', 'yyyymmdd') likai, 'A' lvke from dual 
Union all select '101', to_date (' 20170104 ', 'yyyymmdd'), to_date (' 20170106 ', 'yyyymmdd'), 'B' from dual 
Union all select '123', to_date (' 20170104 ', 'yyyymmdd'), to_date (' 20170110 ', 'yyyymmdd'), 'C' from dual); 

The create table JiuDianNew as 
(select '101', fangJian to_date (' 20170102 ', 'yyyymmdd') daoda, to_date (' 20170103 ', 'yyyymmdd') likai, 'P' lvke from dual 
Union all select '101', to_date (' 20170107 ', 'yyyymmdd'), to_date (' 20170202 ', 'yyyymmdd'), 'Q' from dual); 

Insert into JiuDian 
Select * from j. (select fangjian, Max (daoda) daoda, Max (likai) likai from JiuDian group by fangjian) t, JiuDianNew j 
Where t.f angJian=j.f angJian 
And t.l ikaicommit; 

Select * from JiuDian. 
Drop table JiuDian; 
Drop table JiuDianNew;

I this is an example, for reference!

Thank you for your answer ~ ~ the trouble is that require only redesigned table, and below the INSERT statement cannot be modified,,, that is to say after the table has been modified, the INSERT statement at the bottom of the can work normally, will not have this bug,,,

CodePudding user response:

reference 4 floor POM_24 response:

write a stored procedure to judge it is good, considering you said not to variables, then straight view to create two tables

 create table JiuDian as 
(select '101', fangJian to_date (' 20170101 ', 'yyyymmdd') daoda, to_date (' 20170103 ', 'yyyymmdd') likai, 'A' lvke from dual 
Union all select '101', to_date (' 20170104 ', 'yyyymmdd'), to_date (' 20170106 ', 'yyyymmdd'), 'B' from dual 
Union all select '123', to_date (' 20170104 ', 'yyyymmdd'), to_date (' 20170110 ', 'yyyymmdd'), 'C' from dual); 

The create table JiuDianNew as 
(select '101', fangJian to_date (' 20170102 ', 'yyyymmdd') daoda, to_date (' 20170103 ', 'yyyymmdd') likai, 'P' lvke from dual 
Union all select '101', to_date (' 20170107 ', 'yyyymmdd'), to_date (' 20170202 ', 'yyyymmdd'), 'Q' from dual); 

Insert into JiuDian 
Select * from j. (select fangjian, Max (daoda) daoda, Max (likai) likai from JiuDian group by fangjian) t, JiuDianNew j 
Where t.f angJian=j.f angJian 
And t.l ikaicommit; 

Select * from JiuDian. 
Drop table JiuDian; 
Drop table JiuDianNew;

I this is an example, for reference!