在SQL中，如何為列中的每個條目保存一個數字陣列？

我正在嘗試做一些非常簡單的事情，但不知道怎么做。

假設我有一個名為“任務”的表，其中每個任務都有一組選定的用戶來執行它。用戶列中的數字是指“用戶”表的 ID 列。

任務表：

 -------------- ------------- -------------- 
| 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 中沒有“Array”列型別。

uj5u.com熱心網友回復：

創建橋接表：

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;

并且該表被限制為僅包含有效用戶和唯一的任務/用戶對。

uj5u.com熱心網友回復：

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>

uj5u.com熱心網友回復：

由于這里的答案已經被接受，因此可以使用一個選項將資料從 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

結果：

問候...

標籤：数据库甲骨文

上一篇：如何在存盤程序中生成多行？

下一篇：希望子查詢回傳非空值