Блог

Главная - Вопросы по программированию - Как создать идентификационный номер без дублирования

Как создать идентификационный номер без дублирования

#sql #oracle #window-functions

#sql #Oracle #окно-функции

Вопрос:

У меня есть таблица, вызываемая Student с этими столбцами:

 First name 
Last name
Class ID
Class

Я хочу создать идентификатор для student, я пытался использовать Row_number Over Partiton , но, похоже, я не получаю то, что ищу, но большая часть его сбрасывается, это результат, который я пытаюсь получить.

 First Last   Class    ClasID  ID (outcome looking for)
------------------------------------
John  Brown  Math     M21     ID01
John  Brown  English  E31     ID01
Tom   Bank   Math     M21     ID02
John  Brown  Gym      G41     ID01
Tim   Brown  English  E31     ID03

Комментарии:

1. Эта таблица не должна вызываться Student в первую очередь. Должно быть три таблицы: Student (по одной строке на каждого учащегося, с индивидуальными данными для каждого учащегося, включая a student_id ), Class (по одной строке на класс, с характеристиками каждого класса, включая a class_id ), и таблица, подобная той, которую вы проиллюстрировали, называется, возможно Student_Class , перечисляя только пары (student_id, class_id) , чтобы показать, какие классыкаждый студент зачислен. Итак, вопрос: это то, что вы придумали? Это назначение в вашем классе?

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

Ответ №1:

Используйте dense_rank() :

 select t.*, dense_rank() over(order by last, first) as id
from student

Это присваивает то же id самое строкам, в которых имена и фамилии совпадают.

Если вам действительно нужен формат, подобный IDnn (что, на мой взгляд, не имеет большого смысла), то вы можете отформатировать и объединить:

 select t.*, 
    'ID' || to_char(dense_rank() over(order by last, first), '09') as id
from student

При этом не удастся сгенерировать правильную строку, если имеется более 99 разных учащихся.

Ответ №2:

Решение dense_rank, предлагаемое @GMB, создает «идентификатор» как часть заданного набора результатов, а не как неотъемлемый идентификатор данного учащегося. Классический метод постоянного присвоения идентификатора объекту (учащемуся, сотруднику, классу и т. Д.) Заключается в создании ПОСЛЕДОВАТЕЛЬНОСТИ, А затем ТРИГГЕРА on_insert для заполнения столбца ID значением next_value последовательности.

 SQL> CREATE TABLE STUDENTS
  2  (
  3    STUDENT_ID NUMBER NOT NULL
  4  , FIRST_NAME VARCHAR2(20)
  5  , LAST_NAME VARCHAR2(20)
  6  , CONSTRAINT STUDENTS_PK PRIMARY KEY
  7    (
  8      STUDENT_ID
  9    )
 10    ENABLE
 11  );

Table created.

Elapsed: 00:00:00.05
SQL> --
SQL> CREATE SEQUENCE id_seq
  2   START WITH     1
  3   INCREMENT BY   1
  4  ;

Sequence created.

Elapsed: 00:00:00.01
SQL> CREATE OR REPLACE TRIGGER STUDENTS_TRIG
  2  BEFORE INSERT ON students
  3  FOR EACH ROW
  4  BEGIN
  5    :new.student_ID := ID_SEQ.NEXTVAL;
  6  END;
  7  /

Trigger created.

Elapsed: 00:00:00.02

SQL> show errors
No errors.
SQL> --
SQL> insert into students (first_name,
  2                        last_name)
  3              values ('Ed',
  4                      'Stevens'
  5                     )
  6  ;

1 row created.

Elapsed: 00:00:00.18
SQL> commit;

Commit complete.

Elapsed: 00:00:00.01
SQL> select * from students;

STUDENT_ID FIRST_NAME           LAST_NAME
---------- -------------------- --------------------
         1 Ed                   Stevens

1 row selected.

Elapsed: 00:00:00.07
SQL> --
SQL> drop trigger students_trig;

Trigger dropped.

Elapsed: 00:00:00.02
SQL> drop table students purge;

Table dropped.

Elapsed: 00:00:00.05
SQL> drop sequence id_seq;

Sequence dropped.

Elapsed: 00:00:00.01

Начиная с версии 12, вам не нужно создавать последовательность или триггер, просто объявите столбец ID столбцом ‘identity’. Я оставляю это в качестве упражнения для студента, чтобы исследовать эту концепцию.

Метки: