在SQL(包括但不限于Oracle)中,你需要预先知道输出结果中有多少列,因此对于未知数量的每个经理下属员工来说,动态生成相应数量的列是不可能的。
如果你只想展示每个经理前两个员工的信息,你可以使用以下查询:
SELECT *
FROM (
SELECT manager AS empid,
empid AS emp,
COUNT(*) OVER (PARTITION BY manager) AS cnt,
ROW_NUMBER() OVER (PARTITION BY manager ORDER BY empid) AS rn
FROM employee
WHERE manager = 'EM2'
)
PIVOT (
MAX(emp)
FOR rn IN (1 AS emp1, 2 AS emp2)
);
假设你有如下示例数据:
CREATE TABLE employee (ID, EMPID, NAME, manager) AS
SELECT 1, 'EM1', 'ana', 'EM3' FROM DUAL UNION ALL
SELECT 2, 'EM2', 'john', NULL FROM DUAL UNION ALL
SELECT 3, 'EM3', 'ravi', 'EM2' FROM DUAL UNION ALL
SELECT 4, 'EM4', 'das', 'EM2' FROM DUAL UNION ALL
SELECT 5, 'EM5', 'michael', 'EM3' FROM DUAL;
上述查询将会输出:
| EMPID | CNT | EMP1 | EMP2 |
| --- | --- | --- | --- |
| EM2 | 2 | EM3 | EM4 |
如果你想展示某个经理的所有员工信息(并附带总数),可以考虑用行而不是列来表示:
SELECT manager AS empid,
COUNT(*) OVER (PARTITION BY manager) AS cnt,
ROW_NUMBER() OVER (PARTITION BY manager ORDER BY empid) AS index_of_emp,
empid AS emp
FROM employee
WHERE manager = 'EM2';
这个查询将会输出:
| EMPID | CNT | INDEX_OF_EMP | EMP |
| --- | --- | --- | --- |
| EM2 | 2 | 1 | EM3 |
| EM2 | 2 | 2 | EM4 |
示例链接