Oracle - How to select records based on below logic?

I have:

 Table A
 -------
 N0       Date        Time
 -----------------------------
 123    20-Apr-11      10:00:05
 123    20-Apr-11      10:00:06
 456    20-Apr-11      10:00:01
 456    20-Apr-11      10:00:02

 Table B
 -------
 N0       Date        Time
 -----------------------------
 123    20-Apr-11      10:00:02
 123    20-Apr-11      10:00:04
 123    20-Apr-11      10:00:05
 123    20-Apr-11      10:00:07
 123    20-Apr-11      10:00:08
 456    20-Apr-11      10:00:04
 456    20-Apr-11      10:00:05
 456    20-Apr-11      10:00:02
 456    20-Apr-11      10:00:03
 456    20-Apr-11      10:00:00

Desired Result

 A.N0     A.Date       A.Time     B.Time
 ----------------------------------------
 123    20-Apr-11      10:00:05  10:00:07   
 123    20-Apr-11      10:00:06  10:00:08
 456    20-Apr-11      10:00:01  10:00:03
 456    20-Apr-11      10:00:02  10:00:04

Note

If you see above results there are B.time should be greater than A.time and should not repeat in join.

Please suggest me how to do this with oracle SQL stat开发者_如何学Cement.

Here is something that will give you the output you requested:

With NumberedA As
    (
    Select *
        , Row_Number() Over ( Partition By N0, Date Order By Time ) Num
    From TableA
    )
    , NumberedB As
    (
    Select A.N0, A.Date
        , A.Time ATime
        , B.Time BTime
        , Row_Number() Over ( Partition By A.N0, A.Date, A.Time Order By B.Time ) Num
    From TableA A
        Join TableB B
            On B.N0 = A.N0
                And B.Date = A.Date
                And B.Time > A.Time
    )
Select A.N0, A.Date, A.Time, B.Time
From NumberedA A
    Join NumberedB B
        On B.N0 = A.N0
            And B.Date = A.Date
            And B.ATime = A.Time
            And B.Num = A.Num

That said, it would help if we understood the reasoning behind your request.

Check out this

But required to create one new column called 'Flag' in Table B

CREATE OR REPLACE PROCEDURE Proc_test
IS
BEGIN
   FOR i IN (SELECT   *
                 FROM a
             ORDER BY NAME, date, time)
   LOOP
      FOR j IN (SELECT   *
                    FROM b
                ORDER BY NAME, date, time)
      LOOP
         IF     i.NAME = j.NAME
            AND i.date = j.date
            AND j.flag IS NULL
            AND i.time < j.time
         THEN
            INSERT INTO target
                        (NAME, date, time1, time2
                        )
                 VALUES (i.NAME, i.date, i.time, j.time
                        );

            UPDATE b
               SET flag = 'Y'
             WHERE NAME = i.NAME AND date = i.date AND time = j.time;

            COMMIT;
         END IF;
      END LOOP;
   END LOOP;
END;
/

It is working fine....