Database table s_position and e_position two fields, and are used to store actually position and end position, now need to query the position exists overlapping part of the data in the table, such as:
ID s_position e_position
1 0 15

2 July 29
3 31 40
4 45 to 50

Can find out as long as there is between data repeat,,, ask ace to give directions

CodePudding user response:

In the end is to show what is the result?

CodePudding user response:

Select a.s _position, b.e _position from table a and table b where a.s _position=b.e _position will bring at least square levels of data accumulated (not suitable for big table)

CodePudding user response:

Consider using LAG function, the principle is to put the S_POSITION and E_POSITION line, and then press the POSITION, if an ID is smaller than the previous one after ID, is to grab the overlap of

SELECT *
The FROM (SELECT ID,
The POSITION,
LEAD (ID, 1, 0) OVER (ORDER BY POSITION) AS NEXTID
The FROM (SELECT ID, S_POSITION AS POSITION
FROM the TABLE
The UNION
SELECT ID, E_POSITION AS POSITION
FROM the TABLE))
WHERE ID & gt; NEXTID
AND NEXTID & lt;> 0

CodePudding user response:

The select t1. *, decode (sign (s_position - lag (e_position) over (order by s_position)), 1, 'overlapping') ll from TAB t1;

CodePudding user response:

Select * from table where s_position=e_position, not like this?