The parent ID ID department
1 A company
2 A division 1
3 part B 1
4 1 a part 2
5 A department 2 2
6 part B 1 3
7 A section 11 4
Table 2: the worker table
Department name
Lee A part A of the
A king of B
1 a part V
Part a 1 D
A department 2 E
Part B E1
A department of 11 K1
Part B Q
Part B S
Requirements: part A and part B (parentid=1) the total number of
Department number
The six part A
3 B
CodePudding user response:
6 how to part A of the, isn't it contains 1 A of these data,CodePudding user response:
A department is 6 people, all the people will consist of A subordinate,
CodePudding user response:
The CREATE TABLE TABLE # department (id int, department VARCHAR (10), the parent id int)
The CREATE TABLE # worker TABLE (department VARCHAR (10), name VARCHAR (10))
INSERT INTO table # department VALUES (1, 'A company, null)
INSERT INTO table # department VALUES (2, 'part A', 1)
INSERT INTO table # department VALUES (3, part B, 1)
INSERT INTO table # department VALUES (4, 'part 1 a, 2)
INSERT INTO table # department VALUES (5, 'A part 2, 2)
INSERT INTO table # department VALUES (6, part B 1, 3)
INSERT INTO table # department VALUES (7, 'A part 11, 4)
INSERT INTO table # worker VALUES (' part A ', 'A' lee)
INSERT INTO # worker table VALUES (' part A ', 'B' king)
INSERT INTO table # worker VALUES (in part a of this' 1 ', 'V')
INSERT INTO table # worker VALUES (in part a of this' 1 ', 'D')
In part A of this INSERT INTO # worker table VALUES (' 2 ', 'E')
INSERT INTO table # worker VALUES (' B ', 'E1)
INSERT INTO # worker table VALUES (' part A 11 ', 'K1')
INSERT INTO table # worker VALUES (' B ', 'Q')
INSERT INTO table # worker VALUES (' B ', 'S')
;
WITH ct
AS
(
SELECT *, department AS the FROM x # department table WHERE the parent ID=1
UNION ALL
SELECT b.i d, b., b. parent ID, x FROM ct a INNER JOIN table b # department ON Anderson, d=b. parent ID
)
SELECT x AS department, COUNT the number of (1) the AS
The FROM ct a INNER JOIN table b # worker ON b.=a. departments
GROUP BY x
DROP TABLE # worker TABLE
DROP TABLE TABLE # department