sql - 优化查询以获取最小值/最大值

Question

我的数据为

员工:

id  Name
--------
1   xyz
2   abc
3   qaz

Employee_A:(Eid - 员工表，title - 职称表)

eid    active    type    title
------------------------------
1     1         1        1
1     1         2        2
1     1         4        3
2     0         3        4
2     1         2        2
2     0         6        5
3     0         1        1
3     0         6        5
3     0         4        3

标题:

id  rank title_name
--------------------
1   1     Manager
2   1     Asst Manager
3   2     Staff1
4   3     Staff2
5   4     Staff3

我的查询看起来像

SELECT name, t.title_name
FROM Employee e
INNER JOIN Employee_A a ON e.id = a.eid
INNER JOIN title t on a.title = t.id
WHERE a.type in (1,2) and active = 1
and t.rank = /* step 1 */
(select min(rank) FROM Employee_A a1 INNER JOIN title t1 ON a1.title = t1.id 
 WHERE a1.type in (1,2) and active = 1 AND a1.eid = e.id)
AND a.type = /* step 2 */
    ( select min(type) FROM Employee_A a2 INNER JOIN title t2 on a2.title = t2.id
      WHERE a2.type in (1,2) and active = 1 and a2.eid = e.id
     and t2.rank = 
         (select min(rank) FROM Employee_A a3 INNER JOIN title t3 ON a3.title = t3.id 
          WHERE a3.type in (1,2) and active = 1 AND a3.eid = e.id)
    )

我有超过 400K 的记录，我正在尝试查询具有最小排名的最小类型(2 个标题具有相同的排名，因此需要在它们之间选择最小标题)。我的查询花费了更多时间，我想对其进行优化，有没有办法可以避免步骤 1、2 并使其更简单、更快？

输出:

Name   title_name
-----------------
xyz    Manager
abc    Asst Manager

Answer 1

这能满足您的需要吗？

;WITH cte AS
(
SELECT name, t.title_name, 
  row_number() over (partition by e.id order by rank, type) rn
FROM Employee e
INNER JOIN Employee_A a ON e.id = a.eid
INNER JOIN title t on a.title = t.id
WHERE a.type in (1,2) and active = 1
)

select name,  title_name from cte
where rn=1

或者其他选择

SELECT e.name, t.title_name
FROM Employee e 
CROSS APPLY 
(
SELECT TOP (1) title_name 
FROM Employee_A a 
INNER JOIN title t on a.title = t.id
WHERE a.type in (1,2) and active = 1 AND e.id = a.eid
order by rank, type
) t