关于加入的采访问题 [英] Question Asked in interview on Join

问题描述

我今天在采访中被问到一个问题

I was asked in interview a question today

create table EmployeeDetails
(
  Id int primary key not null,
  EmployeeName varchar(50)
)

create table EmployeeSalary
(
  
  Id int references EmployeeDetails(Id),
  YearofSalray varchar(10),
  EmployeeSalary int
)

alter table EmployeeSalary
add  MonthOfSalary  varchar(10)


Id	YearofSalray	EmployeeSalary	MonthOfSalary
1	2013	             3000	jan
1	2013	             4000	feb
1	2013	             4000	feb
2	2013	             3000	jan
2	2013	             3000	feb
3	2013	             3000	jan
3	2013	             3000	feb
3	2013	             6000	mar

- 输出应该是员工姓名谁在2013年赚了超过10000

- 从上面它应该是Anurag和Siba



我用两种方式回答他们使用cte和子查询。

--Output Should be Employee Name who earned more than 10000 in 2013
--From Above it should be Anurag and Siba

I answered them in two ways using cte and subquery.

select empd.EmployeeName from EmployeeDetails Empd
join
(select SUM(employeesalary) as Salary,id as Id from EmployeeSalary
 group by id
)A on  Empd.Id= A.Id
where A.Salary > 10000

2)
with cte(salary,id) as
(
select SUM(employeesalary) as Salary,id as Id from EmployeeSalary
 group by id
)
select empd.EmployeeName from cte c
join EmployeeDetails Empd on Empd.Id=c.id
where c.salary > 10000

3)CTE subquery
with cte(salary,id) as
(
select SUM(employeesalary) as Salary,id as Id from EmployeeSalary
group by id
)
select EmployeeName from EmployeeDetails where id in
(select id from cte c
where c.salary > 10000)

< br $> b $ b



但他们要求我使用join / left join来使用single select子句。他们要我不要使用子查询和cte。是否可以在单选条款中进行？如果可以使用join / left join使用single select子句来帮助我。

But they were asking me to use single select clause using join/left join. They were asking me not to use subquery and cte. Is it possible to do in single select clause ? Kindly help me if it can be done using single select clause using join/left join.

推荐答案

请阅读： SQL连接的可视化表示 [ ^ ]我向你保证，你会找到答案;）



提示：要使用单个 select 语句获取结果，需要使用 HAVING子句 [ ^ ]。

Please, read this: Visual Representation of SQL Joins[^] and i promise you, you'll find an answer ;)

Tip: To get result by using single select statement, you need to use HAVING clause[^].

SELECT E.EmployeeName, SUM(S.employeesalary) as Salary
FROM EmployeeDetails AS E RIGHT JOIN EmployeeSalary AS S on  E.Id= S.Id
GROUP BY E.EmployeeName
HAVING SUM(S.employeesalary)>1000
--ORDER BY Field 

这篇关于关于加入的采访问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！