如何使用Listagg將資料放入Oracle中

我目前有一個問題：指定擁有> = 2名員工的部門，在每個部門之后列印出部門員工串列。我有以下表格

表員工：

EmpNo number CONSTRAINT PK_EmpNo PRIMARY KEY ,
EmpName varchar2(30),
Birthday DATE not null,
DeptNo number,
MgrNo varchar2(30) not null,
StartDate DATE not null,
Salary number(7,2) not null,
SYS_Level int,
Status int,
CONSTRAINT ck_SYS_Level CHECK  (SYS_Level > 0 AND SYS_Level < 8), 
CONSTRAINT ck_Status CHECK (Status >= 0 AND Status <= 2), 
Note varchar2(4000)

表部：

DeptNo int CONSTRAINT PK_DeptNo PRIMARY KEY,
DeptName varchar(30) not null,
Note varchar2(4000)

我在這兩個表中插入了資料：

INSERT INTO EMPLOYEE (EmpNo, EmpName, BirthDay, DeptNo, MgrNo, StartDate, Salary, SYS_Level, Status, Email)
VALUES (1, 'Changed Dragon', TO_DATE('23-MAR-2000','DD-MON-YYYY'), 1, 'A02', TO_DATE('21-JUN-2020','DD-MON-YYYY'), 400, 2, 0, '[email protected]');
INSERT INTO  EMPLOYEE (EmpNo, EmpName, BirthDay, DeptNo, MgrNo, StartDate, Salary, SYS_Level, 
Status, Email)
VALUES (2, 'Anna Lily', TO_DATE('20-MAY-1999','DD-MON-YYYY'), 3, 'L05', TO_DATE('16-NOV-2021','DD-MON-YYYY'), 600, 3, 0, '[email protected]');
INSERT INTO  EMPLOYEE (EmpNo, EmpName, BirthDay, DeptNo, MgrNo, StartDate, Salary, SYS_Level, Status, Email)
VALUES (3, 'Michael Gray', TO_DATE('14-MAR-2002','DD-MON-YYYY'), 2, 'C08', TO_DATE('08-JAN-2021','DD-MON-YYYY'), 100, 1, 1, '[email protected]');
INSERT INTO EMPLOYEE (EmpNo, EmpName, BirthDay, DeptNo, MgrNo, StartDate, Salary, SYS_Level, Status, Email)
VALUES (4, 'Arthur Gray', TO_DATE('18-OCT-2002','DD-MON-YYYY'), 4, 'C08', TO_DATE('14-FEB-2022','DD-MON-YYYY'), 200, 2, 2, '[email protected]');
INSERT INTO EMPLOYEE (EmpNo, EmpName, BirthDay, DeptNo, MgrNo, StartDate, Salary, SYS_Level, Status, Email)
VALUES (5, 'Tommy Gray', TO_DATE('24-NOV-2002','DD-MON-YYYY'), 5, 'L05', TO_DATE('11-NOV-2021','DD-MON-YYYY'), 400, 5, 1, '[email protected]');
INSERT INTO EMPLOYEE (EmpNo, EmpName, BirthDay, DeptNo, MgrNo, StartDate, Salary, SYS_Level, 
Status, Email)
VALUES (6, 'Ada Thone', TO_DATE('19-MAR-1998','DD-MON-YYYY'), 6, 'C08', TO_DATE('06-MAR-2022','DD-MON-YYYY'), 600, 3, 0, '[email protected]');
INSERT INTO EMPLOYEE (EmpNo, EmpName, BirthDay, DeptNo, MgrNo, StartDate, Salary, SYS_Level, Status, Email)
VALUES (7, 'The Rock', TO_DATE('05-JUL-1999','DD-MON-YYYY'), 7, 'A02', TO_DATE('15-SEP-2022','DD-MON-YYYY'), 800, 3, 0, '[email protected]');
INSERT INTO EMPLOYEE (EmpNo, EmpName, BirthDay, DeptNo, MgrNo, StartDate, Salary, SYS_Level, Status, Email)
VALUES (8, 'Han Sara', TO_DATE('21-NOV-2002','DD-MON-YYYY'), 1, 'L05', TO_DATE('21-JUL-2022','DD-MON-YYYY'), 700, 5, 1, '[email protected]');
INSERT INTO EMPLOYEE (EmpNo, EmpName, BirthDay, DeptNo, MgrNo, StartDate, Salary, SYS_Level, Status, Email)
VALUES (9, 'KIM Jisoo', TO_DATE('01-SEP-1996','DD-MON-YYYY'), 6, 'L05', TO_DATE('17-MAY-2021','DD-MON-YYYY'), 900, 1, 2, '[email protected]'); 

表部門資料：

INSERT INTO DEPARTMENT ( DeptNo, DeptName)
VALUES ( 1, 'Tester');
INSERT INTO DEPARTMENT ( DeptNo, DeptName)
VALUES ( 2, 'DevOps');
INSERT INTO DEPARTMENT ( DeptNo, DeptName)
VALUES ( 3, 'Test Lead');
INSERT INTO DEPARTMENT ( DeptNo, DeptName)
VALUES ( 4, 'CTO');
INSERT INTO DEPARTMENT ( DeptNo, DeptName)
VALUES ( 5, 'Tester');
INSERT INTO DEPARTMENT ( DeptNo, DeptName)
VALUES ( 6, 'Test Manager');
INSERT INTO DEPARTMENT ( DeptNo, DeptName)
VALUES ( 7, 'Senior');
INSERT INTO DEPARTMENT ( DeptNo, DeptName)
VALUES ( 8, 'DevOps');
INSERT INTO DEPARTMENT ( DeptNo, DeptName)
VALUES ( 9, 'Tester');

這是我嘗試過的代碼：

SELECT D.DeptName, listagg(EmpName, ' , ') WITHIN GROUP(ORDER BY EmpName) AS Emps FROM 
DEPARTMENT D
JOIN EMPLOYEE E ON (E.DeptNo = D.DeptNo)
GROUP BY D.DeptName
HAVING COUNT(*) >= 2;

結果如下：

問題是我想要在每一行中顯示的結果，例如名字 KIM Jisoo 將低于名字 Ada Thone，這是我想要的結果，雖然我使用了 CHR(10) 但沒有作業

有什么建議么？謝了。

uj5u.com熱心網友回復：

好吧，它可以在 SQL*Plus 中運行：

SQL> select d.dname,
  2         listagg(e.ename, chr(10)) within group (order by e.ename) emps
  3  from emp e join dept d on e.deptno = d.deptno
  4  group by d.dname;

DNAME          EMPS
-------------- ------------------------------
ACCOUNTING     CLARK
               KING
               MILLER

RESEARCH       ADAMS
               FORD
               JONES
               SCOTT
               SMITH

SALES          ALLEN
               BLAKE
               JAMES
               MARTIN
               TURNER
               WARD


SQL>

我假設您使用了一些 GUI，其輸出“隱藏”了您所做的事情。如果是 SQL Developer，則雙擊結果中的任意位置 - 您將在該欄位的右側看到一個類似鉛筆的按鈕。單擊它，您將看到“正確”的結果。

或者，將代碼作為腳本運行（如果這樣的輸出讓您滿意）。您可能需要設定一些額外的設定（例如列格式、頁面大小、行大小等）。閱讀set檔案中的 SQL*Plus 命令。

標籤：数据库 甲骨文

上一篇：除了no_data_needed之外，還有其他例外被oracle捕獲嗎？

下一篇：基于blob欄位中的值的OracleSQL分組