java - 使用 SQL 查询或 Java 根据条件对 ArrayList 进行排序

Question

I have one employee table in Database have columns emp_id , superior_id, name etc.

I am trying to generate tree using js library.

every employee has superior_id except manager. so the js input I want should be like

manager names --
lead name--
employee name
employee name
employee name
lead name<br/>
employee name
lead name
employee name

但是我得到的结果就像

manager name
lead name
lead name<br/>
lead name
employee name
employee name
employee name
employee name
employee name

SQL query I have written is

SELECT 
  t1.emp_id, 
  t1.emp_name, 
  t1.tcs_mail, 
  t1.boeing_mail, 
  t1.contact_no, 
  t2.emp_id as superior_id
FROM employee_master AS t1
LEFT JOIN employee_master AS t2 ON t2.emp_id = t1.superior_id
LEFT JOIN employee_master AS t3 ON t3.emp_id = t2.superior_id
ORDER BY t3.emp_id,t2.emp_name LIMIT 10

请帮我解决这个问题。

Answer 1

你的查询不正确 - 如果你真的想构建一棵树，你需要能够为你提供一棵树的查询，这意味着分层 SQL 查询。

不幸的是，我并没有真正找到用 MySQL 方言构建分层查询的简单方法。如果您只知道树的嵌套级别始终不超过三个，则可以使用多个计数器来模拟层次结构:

with first_level(emp_id, emp_name, rnum, rnum2, rnum3) as (
  -- first we retrieve all employees that do not have any superiors
  -- there first counter is ordering of those employees, other counters are zero (see below)
    select emp_id, emp_name, ROW_NUMBER() OVER (order by emp_name) as rnum, 0 as rnum2, 0 as rnum3
    from employee_master
    where superior_id is null
  ),
  second_level(emp_id, emp_name, rnum, rnum2, rnum3) as (
  -- then we need subordinates of top superiors. There first counter should represent ordering of the superior
  -- second counter should represent ordering of the subordinate and last counter is zero
    select s.emp_id, s.emp_name, f.rnum, ROW_NUMBER() over (order by f.rnum, s.name) as rnum2, 0 as rnum3
    from employee_master s
    inner join first_level f on f.emp_id = s.superior_id
  ),
  third_level(emp_id, emp_name, rnum, rnum2, rnum3) as (
  -- our last query level. It will have first two counters joined from higher levels and last counter will be the own level ordering
    select s.emp_id, s.emp_name, f.rnum, f.rnum2, row_number() over (order by f.rnum, f.rnum2, s.name) as rnum3
    from employee_master s
    inner join second_level f on f.emp_id = s.superior_id
  )

-- lastly, we combine all of the above into a single query:
select
  emp_id,
  emp_name,
  rnum, rnum2, rnum3
from first_level
union all
select
  emp_id,
  emp_name,
  rnum, rnum2, rnum3
from second_level
union all
select
  emp_id,
  emp_name,
  rnum, rnum2, rnum3
from third_level
order by rnum, rnum2, rnum3

请注意，由于我使用了 WITH 子句，因此上述查询仅在 MySQL 8.0 及更高版本上有效。

您还可以在此处查看更通用的解决方案:ExplainExtended:Hierarchical Queries in MySQL

编辑:这里还有一些关于如何在 MySQL 8.0 及更高版本中构建分层自连接查询的 Material :MySQL Server Team:Recursive Common Table Expressions 。这可能会帮助您解决可能无限的最大树高度的问题，但看起来它可能需要您跳一些圈子才能实现正确的排序(例如，获取当前的树嵌套级别，将其连接到 varchar 中并对其进行排序)。