SELECT department_id "Dept.",
LISTAGG(last_name, '; ') WITHIN GROUP (ORDER BY hire_date) "Employees"
FROM employees
GROUP BY department_id
--以上为主功能
ORDER BY department_id;
Dept. Employees
------ ------------------------------------------------------------
10 Whalen
20 Hartstein; Fay
30 Raphaely; Khoo; Tobias; Baida; Himuro; Colmenares
40 Mavris
50 Kaufling; Ladwig; Rajs; Sarchand; Bell; Mallin; Weiss; Davie
department_id相同情况下,将last_name以“;”连接起来,连接时以hire_date排序之后的顺序进行combine
在不使用group by的时候可以用over语法代替
SELECT department_id "Dept.",
LISTAGG(last_name, '; ') WITHIN GROUP (ORDER BY hire_date) "Employees" <span style="background-color: rgb(255, 0, 0);">OVER(PARTITION BY department_id)</span>
FROM employees
over的用法可以解决一个问题:group by的时候select字段选择的时候字段必须在聚集函数中或者group by字段中。用over方法可以在select字段中加任意表中的字段。