H2DB中带有WITH-CLAUSE的子查询 [英] Sub Query with WITH-CLAUSE in H2DB
本文介绍了H2DB中带有WITH-CLAUSE的子查询的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有一个简单的SQL查询来计算部门中(子级内)的所有雇员,例如:
I have a simple SQL query to count all Employees in a Department(within children) like:
With Temp(id) AS
(
Select d.id From DEPARTMENT d
Where d.id = 1
UNION ALL
Select d.id From DEPARTMENT d JOIN Temp te ON d.idDepartment = te.id
)
Select count(*) From
(
Select e.id From Employee e Join Temp te On e.idDepartment = te.id
)
但是我给出了一个错误"StackOverflow",我不知道哪里出错了,您能帮我吗? 有一些测试用例的数据: 餐桌部门:
But i give a error "StackOverflow", I dont know where is mistake, can you help me? there are some data for test case: Table Department :
ID----------departmentName-----------idDepartment(id parent)
1 A 0
2 B 1
餐桌员工:
id----------employeeName------------idDepartment
1 E_1 1
2 E_2 1
3 E_3 2
因此,当我选择部门(A)中的雇员人数时–>结果:3,如果部门B->结果中:1 谢谢!
So when I select quantity of Eployee in a Department(A)--> result : 3, if Department B --> result: 1 Thanks!
推荐答案
我认为我有一个可行的解决方案:
I think I have a solution that works:
create table Department(id int, name varchar(255), idDepartment int);
create table Employee(id int, name varchar(255), idDepartment int);
insert into Department values(1, 'A', 0), (2, 'B', 1);
insert into Employee values(1, 'E1', 1), (2, 'E2', 1), (3, 'E3', 2);
with recursive temp(id) as (
select 1 union all
select d.id from temp te
inner join Department d on d.idDepartment = te.id
)
select count(*) from temp te
inner join Employee e on e.idDepartment = te.id;
drop table Department;
drop table Employee;
这篇关于H2DB中带有WITH-CLAUSE的子查询的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文