公司组织机构图：
员工表：
其中UserId为用户帐号，UserName为姓名，DepartmentId为部门id。
部门表：
其中id为部门Id，DepartmentName为部门名称，ParentDepartmentId为上一级部门id，Layer为在组织机构图中的级别。
兼职表：
其中MapId为orguser表中id，departmentid为所兼职的部门id。
1、编写sql语句实现分页查询显示员工信息，每页显示4条记录，显示第二页的员工，要求显示序号，员工号，姓名，月薪，按照月薪降序排列
2、通过编写java程序实现当在main方法中输入任意一个用户的UserId时候返回当前部门中所有人员的UserId
说明：公司只有第一级是部门，部门下的都是组。如图：如果输入会计B组人员的账号，要输出财务部所有人员的账号。
例如输入”005”会返回”001 002 003 004 005 015 006 007”返回的顺序可能不一样，但是返回的人数必须完整，
由于用户有兼职，所以如果同一个用户出现了2次或以上只返回一次。
答案
1
SELECT (@row_number:=5) AS 序号,
e.departmentid AS 员工号,
e.username AS 姓名,
e.sal AS 月薪
FROM (
SELECT departmentid, username, sal
FROM orguser
ORDER BY sal DESC
LIMIT 4 OFFSET 4
) AS e
CROSS JOIN (SELECT @row_number:=0) AS dummy;
2
package src.com;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
/*
select userid from orguser where departmentid in (6279,6278,6274,6270,6275);
*/
public class code2 {
public static void main(String[] args) throws Exception {
// 注册驱动,获取连接
Class.forName("com.mysql.cj.jdbc.Driver");
String url = "jdbc:mysql://localhost:3306/practice";
String username = "root";
String password = "1234";
Connection connection = DriverManager.getConnection(url, username, password);
/*
* 根据员工id获取顶层部门id
* 先获取员工所在部门，再递归获取部门的父部门，直到获取到顶层部门
* 输入:员工id
* 输出:顶层部门id
* */
String topLevelDepartmentId = getTopLevelDepartmentId(connection, "005");//输入员工id
/*
* 根据顶层部门查询其每个(子部门)并获取每个子部门下的(员工id)
* 先根据顶层部门获取子部门，再递归获取子部门的子部门直到没有下一级，然后根据子部门查询里面的员工id
* 输入:顶层部门id
* 返回:员工id
* */
List<String> employeeIds = getEmployeesInDepartment(connection, topLevelDepartmentId);
for (String employeeId : employeeIds) {
System.out.println("员工id: "+employeeId);
}
connection.close();
}
/*
* 查询当前部门id，并递归查询顶层部门的ID
* 输入:员工id
* 调用:递归方法getTopLevelDepartment()
* 输出:员工所在顶级部门id
*/
private static String getTopLevelDepartmentId(Connection connection, String userId) throws Exception {
// 从员工表查询用户所在的部门
String query = "SELECT departmentid FROM orguser WHERE userid = ?";
PreparedStatement preparedStatement = connection.prepareStatement(query);
preparedStatement.setString(1, userId);
ResultSet resultSet = preparedStatement.executeQuery();
if (resultSet.next()) {
// 当前用户所在部门id
String departmentId = resultSet.getString("departmentid");
// 顶层id
// 调用递归函数getTopLevelDepartment()获取每一个父级部门id
String topLevelDepartmentId = getTopLevelDepartment(connection, departmentId);
return topLevelDepartmentId;
}
// 用户不存在
return null;
}
/*
* 递归方法:递归查询顶层部门的ID
* 输入:当前部门ID
* 输出:当前部门的顶级部门的ID
*/
private static String getTopLevelDepartment(Connection connection, String departmentId) throws Exception {
//根据部门id查询父部门id和当前部门层级
String query = "SELECT parentdepartmentid, layer FROM orgdepartment WHERE id = ?";
PreparedStatement preparedStatement = connection.prepareStatement(query);
preparedStatement.setString(1, departmentId);
ResultSet resultSet = preparedStatement.executeQuery();
if (resultSet.next()) {
// 父部门id
String parentDepartmentId = resultSet.getString("parentdepartmentid");
// 当前部门层级
int layer = resultSet.getInt("layer");
if (layer == 2) {
// 顶层部门
return departmentId;
} else {
// 层级没有到顶级，继续递归
return getTopLevelDepartment(connection, parentDepartmentId);
}
}
// 部门不存在，返回null
return null;
}
/*
* 递归查询指定部门及其子部门下的所有员工ID
* 输入:顶级部门ID
* 输出:部门下(包括子部门)的所有员工ID
*
*/
private static List<String> getEmployeesInDepartment(Connection connection, String departmentId) throws Exception {
List<String> employeeIds = new ArrayList<>();
// 查询部门下的员工ID
List<String> departmentEmployeeIds = getEmployeesInGroup(connection, departmentId);
employeeIds.addAll(departmentEmployeeIds);
// 查询指定部门下的子部门
List<String> subDepartmentIds = getSubDepartments(connection, departmentId);
// 对于每个子部门，递归查询员工ID
for (String subDepartmentId : subDepartmentIds) {
List<String> subDepartmentEmployeeIds = getEmployeesInDepartment(connection, subDepartmentId);
employeeIds.addAll(subDepartmentEmployeeIds);
}
return employeeIds;
}
/*
* 查询指定组下的员工ID
* 输入:员工部门ID
* 输入:部门下的员工ID
*
*/
private static List<String> getEmployeesInGroup(Connection connection, String groupId) throws Exception {
List<String> employeeIds = new ArrayList<>();
String query = "SELECT ou.userid FROM orguser ou WHERE ou.departmentid = ?";
PreparedStatement preparedStatement = connection.prepareStatement(query);
preparedStatement.setString(1, groupId);
ResultSet resultSet = preparedStatement.executeQuery();
while (resultSet.next()) {
employeeIds.add(resultSet.getString("userid"));
}
return employeeIds;
}
/*
* 查询指定部门下的子部门ID
* 需要递归调用到每个子部门的子部门
* 输入:部门ID
* 输出:子部门ID
*/
private static List<String> getSubDepartments(Connection connection, String departmentId) throws Exception {
List<String> subDepartmentIds = new ArrayList<>();
String query = "SELECT id FROM orgdepartment WHERE parentdepartmentid = ?";
PreparedStatement preparedStatement = connection.prepareStatement(query);
preparedStatement.setString(1, departmentId);
ResultSet resultSet = preparedStatement.executeQuery();
while (resultSet.next()) {
subDepartmentIds.add(resultSet.getString("id"));
}
return subDepartmentIds;
}
}
