Optimized query to get min/max
I have data as
Employee:
id Name
--------
1 xyz
2 abc
3 qaz
Employee_A: (Eid - employee table, title - title table)
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
Title:
id rank title_name
--------------------
1 1 Manager
2 1 Asst Manager
3 2 Staff1
4 3 Staff2
5 4 Staff3
My query looks like
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)
)
I have more than 400K records and I'm trying to query minimum type with minimum rank (2 titles have same rank so need to select minimum title between them). My query is taking more time and I want to optimize it, is there a way i can avoid step 1, 2 and do it more simpler and fast?
Output:
Name title_name
-----------------
xyz Manager
abc Asst Manager
Does this do what you need?
;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
Or another alternative
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
I may be oversimplifying, but give this a shot:
select emp.name, MIN(a.title), MIN(t.rank)
from Employee emp
join Employee_A a on emp.id = a.eid
AND a.type in(1,2)
and a.active = 1
join title t on a.title = t.id
where not exists (select 1 from Employee_A where eid = emp.id and title < a.title and active = 1)
group by emp.name
精彩评论