Я пытаюсь сделать что-то очень простое, но не могу понять, как это сделать.
Скажем, у меня есть эта таблица под названием «Задачи», где у каждой задачи есть выбранный набор пользователей, которые должны ее выполнять. Цифры в столбце «Пользователи» относятся к столбцу «Идентификатор» таблицы «Пользователи».
Таблица задач:
+--------------+-------------+--------------+
| Task | Created_On | Users |
+--------------+-------------+--------------+
| Task A | 19/11/22 | 1,3,4 |
| Task B | 19/11/22 | 1,4,5,6 |
| Task C | 19/11/22 | 2,3,6 |
+--------------+-------------+--------------+
Таблица пользователей:
+--------------+-------------+
| ID | User |
+--------------+-------------+
| 1 | George |
| 2 | John |
| 3 | Jim |
| 4 | James |
| 5 | Jill |
| 6 | Joe |
+--------------+-------------+
Как создать столбец «Пользователи» в таблице «Задачи»? В Oracle нет столбца типа "массив".
@BarbarosÖzhan Нет, я просто хочу сохранить список пользователей, связанных с каждой задачей.
Не могли бы вы также добавить желаемый результат?
@BarbarosÖzhan вывод чего?
Я вижу два набора данных из двух разных таблиц, не так ли? Хотите к ним присоединиться и какие моды?
@BarbarosÖzhan Извините, я не правильно написал пост. Обновлено.
@BarbarosÖzhan Я хочу создать таблицу, аналогичную таблице «Задачи», проблема именно в последнем столбце, в котором каждая строка должна содержать список чисел. Как создать этот столбец?
В SQL и ERM вы используете отношения n:m: en.wikipedia.org/wiki/Many-to-many_(data_model) Вам нужно создать промежуточную таблицу, например. Задачи2Пользователи.
@gekart это так просто, но я не мог понять
@ User104163 User104163 Только что опубликовал параметр, который может помочь вам вставить данные в «соединяющую» таблицу. С уважением...
@dr спасибо, но я уже все понял :)
Создайте связующую таблицу:
CREATE TABLE task_users (
Task CONSTRAINT task_users__task__fk REFERENCES tasks (task),
User_id CONSTRAINT task_users__user_id__fk REFERENCES users (id),
CONSTRAINT task_users__task__user_id__pk PRIMARY KEY(task, user_id)
);
Затем вы можете вставить данные:
INSERT INTO task_users (task, user_id)
SELECT 'Task A', 1 FROM DUAL UNION ALL
SELECT 'Task A', 3 FROM DUAL UNION ALL
SELECT 'Task A', 4 FROM DUAL UNION ALL
SELECT 'Task B', 1 FROM DUAL UNION ALL
SELECT 'Task B', 4 FROM DUAL UNION ALL
SELECT 'Task B', 5 FROM DUAL UNION ALL
SELECT 'Task B', 6 FROM DUAL UNION ALL
SELECT 'Task C', 2 FROM DUAL UNION ALL
SELECT 'Task C', 3 FROM DUAL UNION ALL
SELECT 'Task C', 6 FROM DUAL;
и таблица должна содержать только действительных пользователей и уникальные пары задача/пользователь.
В Oracle нет столбца типа "массив"
Верно, но есть еще один встроенный тип данных, который вы можете использовать — sys.odcinumberlist
. Вот как:
SQL> create table task (task varchar2(10), created_on date, users sys.odcinumberlist);
Table created.
SQL> insert into task (task, created_on, users)
2 values ('Task A', date '2022-11-19', sys.odcinumberlist(1,3,4));
1 row created.
SQL> select * from task;
TASK CREATED_ON USERS
---------- ---------- ----------------------------------------
Task A 19.11.2022 ODCINUMBERLIST(1, 3, 4)
SQL>
Итак, что это за пользователи 1, 3 и 4?
SQL> select * from users;
ID USERNAME
---------- ----------
1 George --> this
2 John
3 Jim --> this
4 James --> this
5 Jill
6 Joe
6 rows selected.
SQL> select t.task, u.username
2 from task t join users u on u.id in (select * From table(t.users));
TASK USERNAME
---------- ----------
Task A George
Task A Jim
Task A James
SQL>
Это не налагает никаких ссылочных ограничений на хранилища значений в SYS.ODCINUMBERLIST
varray, поэтому вы можете включить значение -3.14159
, когда никогда не будет пользователя с совпадающим значением id
.
sys.odcinumberlist
действительно является предопределенным типом коллекции, иллюстрирующим, что в SQL есть массивы, но я не уверен, что его рекомендуется использовать в коде приложения. Обычно мы определяем свои собственные, используя что-то вроде create or replace type number_ntt as table of number
.
Поскольку ответ уже принят, вот вариант, который можно использовать для вставки данных в связующую таблицу из ответа MTO. Здесь используется предложение SQL MODEL:
SELECT t.TASK, t.CREATED_ON, t.USERS, t.IDS_COUNT, t.IDX "ID_NO", t.ID, u.USER_NAME
FROM ( Select *
From ( Select TASK, CAST(0 As VarChar2(32)) "ID", USERS, CREATED_ON,
Coalesce(Length(USERS) - Length(Replace(USERS,',',null)), Length(USERS), 0) + 1 "IDS_COUNT"
From tasks
)
MODEL
PARTITION BY (TASK)
DIMENSION BY (0 "IDX")
MEASURES (USERS, CREATED_ON, IDS_COUNT, ID)
RULES ITERATE(6) -- declare bigger number than expected number of elements in list
(
USERS[ITERATION_NUMBER] = USERS[0], CREATED_ON[ANY] = CREATED_ON[0], IDS_COUNT[ANY] = IDS_COUNT[0],
ID[ITERATION_NUMBER] = CASE
WHEN ITERATION_NUMBER BETWEEN 1 And IDS_COUNT[0]
THEN SubStr(SubStr(',' || USERS[0] || ',', InStr(',' || USERS[0] || ',', ',', 1, ITERATION_NUMBER)+1), 1, InStr(',' || USERS[0] || ',', ',', 1, ITERATION_NUMBER+1) - InStr(',' || USERS[0] || ',', ',', 1, ITERATION_NUMBER) - 1)
END
)
) t
INNER JOIN users u ON(u.ID = t.ID) --LEFT JOIN if you want to see possible unmatched IDs - USER_NAME would be Null
Where IDX Between 1 And IDS_COUNT
Order By TASK, IDX
Результат:
С уважением...
Вам нужно объединенное имя пользователя вместо этих чисел в столбце
Users
? (например,George,Jim,James
вместо1,3,4
..и т. д.)