sql - 在 SQL 上使用两个表进行递归查询的正确顺序是什么?
问题描述
我有以下两个表:
CREATE TABLE empleados (
id INTEGER PRIMARY KEY,
nombre VARCHAR(255) NOT NULL,
gerenteId INTEGER,
FOREIGN KEY (gerenteId) REFERENCES empleados(id)
);
CREATE TABLE ventas (
id INTEGER PRIMARY KEY,
empleadoId INTEGER NOT NULL,
valorOrden INTEGER NOT NULL,
FOREIGN KEY (empleadoId) REFERENCES empleados(id)
);
使用以下数据:
INSERT INTO empleados(id, nombre, gerenteId) VALUES(1, 'Roberto', null);
INSERT INTO empleados(id, nombre, gerenteId) VALUES(2, 'Tomas', null);
INSERT INTO empleados(id, nombre, gerenteId) VALUES(3, 'Rogelio', 1);
INSERT INTO empleados(id, nombre, gerenteId) VALUES(4, 'Victor', 3);
INSERT INTO empleados(id, nombre, gerenteId) VALUES(5, 'Johnatan', 4);
INSERT INTO empleados(id, nombre, gerenteId) VALUES(6, 'Gustavo', 2);
INSERT INTO ventas(id, empleadoId, valorOrden) VALUES(1, 3, 400);
INSERT INTO ventas(id, empleadoId, valorOrden) VALUES(2, 4, 3000);
INSERT INTO ventas(id, empleadoId, valorOrden) VALUES(3, 5, 3500);
INSERT INTO ventas(id, empleadoId, valorOrden) VALUES(4, 2, 40000);
INSERT INTO ventas(id, empleadoId, valorOrden) VALUES(5, 6, 3000);
我正在尝试查询以获取直接或不直接属于主要经理的所有“订单”的总和。主要经理是那些不向其他人报告的人。在这种情况下,罗伯托和托马斯是主要经理,但可能还有其他人。结果必须不仅考虑到他直接进行的销售(ventas),还考虑到他们的任何员工(直接员工或员工的员工)。
所以在这种情况下,我期待以下结果:
-- Id TotalVentas
-- ----------------
-- 1 6900
-- 2 43000
其中 Id 列是指员工的 id,即“主要”经理,TotalVentas 列是他们及其员工所做的所有 ventas (valorOrden) 的总和。
所以 Roberto 没有订单记录,但 Rogelio(他的员工)有 400 个订单中的一个,Victor(Rogelio 的员工)有一个 3000 的订单,Johnatan(Victor 的员工)有另一个 3500 的订单。所以它们的总和是 6900。它托马斯的情况也是如此,他直接制作了一个文塔,再加上他的雇员古斯塔沃制作的另一个文塔。
到目前为止,我的查询如下:
WITH cte_org AS (
SELECT
id,
nombre,
gerenteId,
0 as EmpLevel
FROM
dbo.empleados
WHERE gerenteId IS NULL
UNION ALL
SELECT
e.id,
e.nombre,
e.gerenteId,
o.EmpLevel + 1
FROM
dbo.empleados e
INNER JOIN cte_org o
ON o.id = e.gerenteId
WHERE e.gerenteId IS NOT NULL
)
SELECT cte.id, SUM(s.orderValue)
FROM cte_org cte, dbo.sales s
WHERE (cte.id = s.employeeId AND cte.gerenteId is null)
OR
(cte.id = s.employeeId AND cte.EmpLevel <> 0 AND
cte.gerenteId in (select ee.id from dbo.empleados ee where ee.gerenteId is null)
)
--AND
--(cte.gerenteId in (select ee.id from dbo.empleados ee where ee.gerenteId is null)
--OR
--cte.gerenteId is null)
--AND cte.gerenteId = NULL
group by cte.id
;
有人可以帮我吗?
解决方案
这是遍历一个层次结构,从最高级别的经理开始,然后加入销售:
with cte as (
select id, nombre, id as manager
from empleados e
where gerenteid is null
union all
select e.id, e.nombre, cte.manager
from cte join
empleados e
on cte.id = e.gerenteid
)
select cte.manager, sum(valororden)
from cte join
ventas v
on cte.id = v.empleadoid
group by cte.manager;
这是一个 db<>fiddle。Fiddle 使用 SQL Server,因为这与您使用的语法一致。
推荐阅读
- spring - 在 VueJS 中将数据拆分为行和列
- c++ - fdeclspec 未启用 -- cLion Mac
- google-maps - Anmaps - Google Maps 仅用于开发目的
- javascript - p5.js 拖拽对象不兼容 IE EDGE 浏览器
- arrays - 在 React 中使用 push 方法的空数组 setState
- python - python多线程套接字分段错误
- angular - Angular 8 深度嵌套路由,命名出口和延迟加载
- macos - 为什么更大的 Xss 不能提供更大的最大堆栈深度?
- ios - 核心蓝牙在后台模式下连续接收外设数据超过 80 小时
- python-3.x - 命名小部件以自动销毁-替换它