1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69
|
SQL> CREATE TABLE TEST(time_stamp DATE, V1 VARCHAR2(1));
Table créée.
SQL>
SQL> INSERT INTO TEST VALUES('01/01/2006','A');
1 ligne créée.
SQL> INSERT INTO TEST VALUES('02/01/2006','A');
1 ligne créée.
SQL> INSERT INTO TEST VALUES('02/01/2006','C');
1 ligne créée.
SQL> INSERT INTO TEST VALUES('03/01/2006','C');
1 ligne créée.
SQL>
SQL> SELECT * FROM TEST;
TIME_STA V
-------- -
01/01/06 A
02/01/06 A
02/01/06 C
03/01/06 C
SQL>
SQL> SELECT *
2 FROM TEST T1
3 WHERE
4 EXISTS
5 (
6 SELECT
7 1
8 FROM TEST T2
9 WHERE
10 T1.time_stamp=T2.time_stamp
11 AND T1.V1<>(SELECT MIN(V1) FROM TEST T3 WHERE T3.time_stamp=T2.time_stamp)
12 );
TIME_STA V
-------- -
02/01/06 C
SQL>
SQL> DELETE
2 FROM TEST T1
3 WHERE
4 EXISTS
5 (
6 SELECT
7 1
8 FROM TEST T2
9 WHERE
10 T1.time_stamp=T2.time_stamp
11 AND T1.V1<>(SELECT MIN(V1) FROM TEST T3 WHERE T3.time_stamp=T2.time_stamp)
12 );
1 ligne supprimée.
SQL>
SQL> SELECT * FROM TEST;
TIME_STA V
-------- -
01/01/06 A
02/01/06 A
03/01/06 C |
Partager