SQL Server uses recursive query data

Function Description: There are two tables, a Business Unit table A (all the business unit information of a company in Table a), and the other table for user table B (in table B, where all users of a company are in the business Unit information), requiring the use of SQL statements to query the number of users in each department (note: When counting the number of users Need to count the users of all sub-departments below);

Table A:

Table B:

Method Description:

Use a recursive approach to SQL Server:

1, the first joint two tables query data, and the results of the query is stored in the temporary table;

2, using the CTE method to query data from the temporary table;

The specific SQL script is as follows:

　　　　

SelectB. User name, b. Business unit, A. Parent DepartmentInto#Table1From BLeft join A on B. Business unit name = A. primary key;  With CTE  as ( select user name, business unit,
Parent department, from #Table1 c where c. business unit = 102
UNION ALL
Select
User name,
Business Unit,
Parent Department
From CTE D INNER join #Table1 E on D. Business unit = E. Parent department)
Select SUM (user name) number of users
From CTE

drop table #Table1              

SQL Server uses recursive query data