Как в SQL сохранить массив чисел для каждой записи в столбце?

Я пытаюсь сделать что-то очень простое, но не могу понять, как это сделать.

Скажем, у меня есть эта таблица под названием «Задачи», где у каждой задачи есть выбранный набор пользователей, которые должны ее выполнять. Цифры в столбце «Пользователи» относятся к столбцу «Идентификатор» таблицы «Пользователи».

Таблица задач:

+--------------+-------------+--------------+
| 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 нет столбца типа "массив".

Вам нужно объединенное имя пользователя вместо этих чисел в столбце Users? (например, George,Jim,James вместо 1,3,4 ..и т. д.)

Barbaros Özhan 19.11.2022 21:05

@BarbarosÖzhan Нет, я просто хочу сохранить список пользователей, связанных с каждой задачей.

User104163 19.11.2022 21:08

Не могли бы вы также добавить желаемый результат?

Barbaros Özhan 19.11.2022 21:09

@BarbarosÖzhan вывод чего?

User104163 19.11.2022 21:09

Я вижу два набора данных из двух разных таблиц, не так ли? Хотите к ним присоединиться и какие моды?

Barbaros Özhan 19.11.2022 21:10

@BarbarosÖzhan Извините, я не правильно написал пост. Обновлено.

User104163 19.11.2022 21:11

@BarbarosÖzhan Я хочу создать таблицу, аналогичную таблице «Задачи», проблема именно в последнем столбце, в котором каждая строка должна содержать список чисел. Как создать этот столбец?

User104163 19.11.2022 21:13

В SQL и ERM вы используете отношения n:m: en.wikipedia.org/wiki/Many-to-many_(data_model) Вам нужно создать промежуточную таблицу, например. Задачи2Пользователи.

gekart 19.11.2022 21:18

@gekart это так просто, но я не мог понять

User104163 19.11.2022 21:25

@ User104163 User104163 Только что опубликовал параметр, который может помочь вам вставить данные в «соединяющую» таблицу. С уважением...

d r 20.11.2022 11:55

@dr спасибо, но я уже все понял :)

User104163 22.11.2022 08:53
ReactJs | Supabase | Добавление данных в базу данных
ReactJs | Supabase | Добавление данных в базу данных
Это и есть ваш редактор таблиц в supabase.👇
Понимание Python и переход к SQL
Понимание Python и переход к SQL
Перед нами лабораторная работа по BloodOath:
0
11
57
3
Перейти к ответу Данный вопрос помечен как решенный

Ответы 3

Ответ принят как подходящий

Создайте связующую таблицу:

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.

MT0 19.11.2022 22:20
sys.odcinumberlist действительно является предопределенным типом коллекции, иллюстрирующим, что в SQL есть массивы, но я не уверен, что его рекомендуется использовать в коде приложения. Обычно мы определяем свои собственные, используя что-то вроде create or replace type number_ntt as table of number.
William Robertson 20.11.2022 11:24

Поскольку ответ уже принят, вот вариант, который можно использовать для вставки данных в связующую таблицу из ответа 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

Результат:

ЗАДАЧА СОЗДАНО НА ПОЛЬЗОВАТЕЛИ IDS_COUNT ID_NO Я БЫ ИМЯ ПОЛЬЗОВАТЕЛЯ Задача А 19 ноября 22 1,3,4 3 1 1 Джордж Задача А 19 ноября 22 1,3,4 3 2 3 Джим Задача А 19 ноября 22 1,3,4 3 3 4 Джеймс Задача Б 19 ноября 22 1,4,5,6 4 1 1 Джордж Задача Б 19 ноября 22 1,4,5,6 4 2 4 Джеймс Задача Б 19 ноября 22 1,4,5,6 4 3 5 Джилл Задача Б 19 ноября 22 1,4,5,6 4 4 6 Джо Задача С 19 ноября 22 2,3,6 3 1 2 Джон Задача С 19 ноября 22 2,3,6 3 2 3 Джим Задача С 19 ноября 22 2,3,6 3 3 6 Джо

С уважением...

Другие вопросы по теме