Лекции по предмету омбт (Oracle 9i маълумотлар базаси технологияси) Лекция Введение в Oracle 9i. План

Использование группировки для поиска повторяющихся данных.

С помощью группировки записей можно находить дублирующие данные в таблице ORACLE. Создадим таблицу цветов и предположим, что мы случайно поместили туда повторяющиеся записи.

SQL > CREATE TABLE COLOR (

2 COLOR_CD VARCHAR2 (8) NOT NULL,
3 COLOR_DESCR VARCHAR2 (30));
TABLE CREATED.

SQL > INSERT INTO COLOR (COLOR_CD, COLOR_DESCR)

VALUES (‘RD’, ‘RED’);
1 ROWS CREATED.

SQL > INSERT INTO COLOR (COLOR_CD, COLOR_DESCR)

VALUES (‘GN’, ‘GREEN’);
1 ROWS CREATED.

SQL > INSERT INTO COLOR (COLOR_CD, COLOR_DESCR)

VALUES (‘BL’, ‘BLUE’);
1 ROWS CREATED.

SQL > INSERT INTO COLOR (COLOR_CD, COLOR_DESCR)

VALUES (‘GN’, ‘GREEN’);
1 ROWS CREATED.

SQL > INSERT INTO COLOR (COLOR_CD, COLOR_DESCR)

VALUES (‘BK’, ‘BLACK‘);
1 ROWS CREATED.
Отметим что таблица COLOR (цвета) содержит повторяющуюся записи для зелённого цвета.
SQL > SELECT * FROM COLOR;

COLOR CD COLOR_DESCR

RD RED
GN GREEN
BL BLUE
GN GREEN
BK BLACK

С помощью функции GROUP BY найдём те коды цветов, которые встречаются в таблице боле одного раза:

SQL > SELECT COUNT (COLOR_CD), MAX (COLOR_DESCR)

2. 
   FROM COLOR
   
3. 
   GROUP BY COLOR_CD
   
4. 
   HAVING COUNT (COLOR_CD) > 1;
   

COUNT COLOR_CD MAX COLOR_DESCR

2. 
   GREEN
   

Использование группировки для удаления дублирующих записей.

Приведём более сложный пример. Здесь повторяющиеся записи будут определяться дл комбинации столбцов EMP_DI (Идентификатор служащего) и PROJ_ID (Идентификатор проекта) с помощью команды GROUP_BY найдём дублирующие записи и удалим их. Вначале создадим таблицу.

SQL > CREATE TABLE PROJECT TEAM (

2 PROJTEAM_ID INTEGER NOT NULL,

3 EMP_ID INTEGER NOT NULL,
4 PROJ_ID INTEGER NOT NULL,
5 PROJTEAM_NAME VARCHAR2 (20));

TABLE CREATED.

SQL > INSERT INTO PRAJECT_TEAM

2. 
   (PROJTEAM_ID, EMP_ID, PROJ_ID, PROJTEAM_NAME)
   
3. 
   VALUES (1, 10, 221, ‘TIMETRACKING’);
   

1 ROW CREATED

SQL > INSERT INTO PROJECT_TEAM

2. 
   (PROJTEAM_ID, EMP_ID, PROJ_ID, PROJTEAM_NAME)
   
3. 
   VALUES (2, 30, 221, ‘TIMETRACKING’);
   

1 ROW CREATED

SQL > INSERT INTO PROJECT_TEAM

2. 
   (PROJTEAM_ID, EMP_ID, PROJ_ID, PROJTEAM_NAME)
   
3. 
   VALUES (3, 10, 221, ‘TIME TRACKING’);
   

1 ROW CREATED

SQL > INSERT INTO PROJECT_TEAM

2. 
   (PROJTEAM_ID, EMP_ID, PROJ_ID, PROJTEAM_NAME)
   
3. 
   VALUES (4, 10, 505, ‘ACCOUNT SISTEM’);
   

1 ROW CREATED

SQL > INSERT INTO PROJECT_TEAM

2. 
   (PROJTEAM_ID, EMP_ID, PROJ_ID, PROJTEAM_NAME)
   
3. 
   VALUES (5, 40, 505, ‘ACCOUNT SISTEM’);
   

1 ROW CREATED

SQL > INSERT INTO PROJECT_TEAM

2. 
   (PROJTEAM_ID, EMP_ID, PROJ_ID, PROJTEAM_NAME)
   
3. 
   VALUES (6, 102, 21, ‘HR REPORTING’);
   

1 ROW CREATED

Выведем на экран содержимое этой таблицы.

S QL > SELECT * FROM PROJECT_TEAM

Здесь дублирующей является следующая запись.

3 10 221 TIME TRACKING

Действительно, комбинация значений полей EMP_ID и PROJ_ID для этой записи не является уникальной в таблице PROJECT TEAM.

Запустим программу поиска повторяющихся записей.

SQL > SELECT COUNT (EMP_ID | | PROJ_ID), MAX (PROJTEAM_ID)

2. 
   FROM PROJECT_TEAM
   
3. 
   GROUP_BY EMP_ID, PROJ_ID
   
4. 
   HAVING COUNT (EMP_ID | | PROJ_ID) > 1;
   

COUNT (EMP_ID | | PROJ_ID) MAX (PROJTEAM_ID)

--------------------------------------- ------------------------------

2. 
   3
   

Теперь удалим дублирующую запись.

SQL > DELETE FROM PROJECT_TEAM

2. 
   WHERE PROJTEAM_ID = (SELECT MAX (PROJTEAM_ID)
   
3. 
   FROM PROJECT_TEAM
   
4. 
   GROUP BY EMP_ID, PROJ_ID
   
5. 
   HAVING COUNT (EMP_ID | | PROJ_ID) > 1);
   

1 ROW DELETED.

Всё, теперь убедимся, что программа сделала всё правильно.

SQL > SELECT * FROM PROJECT_TEAM

При наличии в таблице нескольких дублирующих записей пришлось бы неоднократно запускать этот оператор DELETE до тех пор, пока записи не перестали бы удаляться.