如何相加（计数）

Question

我想添加一个计数（e.employee_id），然后按Manager_ID对其进行分组，因此我得到的结果是员工数量的

SELECT upper(concat(concat(concat(e2.First_name,' '),' '),e2.Last_Name)) "JEFE",
       e.employee_id
FROM employees e join employees e2 
  on (e.manager_id = e2.employee_id);

结果是：

Name: Mike, Employee_Id: 101
Name: Mike, Employee_Id: 102
Name: Mike, Employee_Id: 103
Name: Mike, Employee_Id: 104
Name: Jason, Employee_Id: 201
Name: Jason, Employee_Id: 202
Name: Jason, Employee_Id: 203
Name: Jason, Employee_Id: 204

我希望结果是：

Name: Mike, Employee_Id: 4
Name: Jason, Employee_Id: 4

我尝试这样做：

SELECT upper(concat(concat(concat(e2.First_name,' '),' '),e2.Last_Name)) "JEFE",
       count(e.employee_id)
FROM employees e join employees e2 
  on (e.manager_id = e2.employee_id)
group by e.manager_id;

但是SQL说：

ora-00979：不是通过表达式的组
00979。00000-“不是按表达式的组”

Answer 1

通过子句将名称添加到组中，或将名称包装在聚合功能中。

您还可以使用层次查询，而不是（较慢的）自加入：

SELECT MAX(UPPER(PRIOR First_name || '  ' || PRIOR Last_Name)) AS manager_name,
       COUNT(employee_id),
       manager_id
FROM   employees
WHERE  LEVEL = 2
CONNECT BY
       PRIOR employee_id = manager_id
GROUP BY
       manager_id;

或：

SELECT UPPER(PRIOR First_name || '  ' || PRIOR Last_Name) AS manager_name,
       COUNT(employee_id),
       manager_id
FROM   employees
WHERE  LEVEL = 2
CONNECT BY
       PRIOR employee_id = manager_id
GROUP BY
       PRIOR first_name,
       PRIOR last_name,
       manager_id;

对于示例数据：

CREATE TABLE employees (employee_id, manager_id, first_name, last_name) AS
SELECT 1, NULL, 'Alice', 'Abbot' FROM DUAL UNION ALL
SELECT 2, 1, 'Beryl', 'Baron' FROM DUAL UNION ALL
SELECT 3, 1, 'Carol', 'Chase' FROM DUAL UNION ALL
SELECT 4, 2, 'Debra', 'Doris' FROM DUAL UNION ALL
SELECT 5, 3, 'Emily', 'Evans' FROM DUAL UNION ALL
SELECT 6, 3, 'Fiona', 'Frank' FROM DUAL UNION ALL
SELECT 7, 6, 'Gemma', 'Grace' FROM DUAL;

这两个输出：

manager_name	count（雇员_id）	manager_id
beryl男爵	1	2
carol chase	2	3
fiona frank	1	6
爱丽丝·阿伯特（Alice Abbot）	2	1

db<>fiddle