时间戳

首页 > 解决方案 > 使用内连接代替子查询

sql - 使用内连接代替子查询

问题描述

我使用子查询编写了一个查询，但我想知道是否有任何方法可以只使用内部连接（或其他连接）来编写它，因为它更有效。

/*2.    List the name of the sales rep who serves the most customers*/

select Sr.REP_NUM, Fname, Lname, count(cust_num) As TotalCustomers from employee Em
inner join SALESREP Sr on Sr.REP_NUM = Em.EMP_Num
inner join Customer Cu on Cu.REP_NUM = Sr.REP_NUM
group by Sr.REP_NUM, fname, lname
having count(Cu.rep_num) = (select max(AllReps.MostReps) from
(select count(rep_num) As MostReps from Customer group by rep_num) As AllReps)

提前致谢。

标签： sqlsql-serverinner-join

解决方案

您可以使用TOP (1)或TOP (1) WITH TIES。这应该比HAVING条款更好：

select top (1) with ties Sr.REP_NUM, em.Fname, em.Lname, count(*) As TotalCustomers
from employee Em join
     SALESREP Sr
     on Sr.REP_NUM = Em.EMP_Num join
     Customer Cu
     on Cu.REP_NUM = Sr.REP_NUM
group by Sr.REP_NUM, fname, lname
order by count(*) desc;

推荐阅读