问题描述
我有4张桌子：
1.部门：
id pk | departName |积极| createdDate
2.employees
id pk |名字|电子邮件|活跃的| createddate | Department_id fk | Designation_Id fk
3.designation
id |名字有效
4.salary
id | salaryIssuedate |金额|扣除| employee_id fk
--------查询----首先----------
让所有员工在2012年至2013年期间获得最高扣除额
------- --query ------ 2nd -----------
让员工在2013年注册到HRM部门的员工，员工薪水应该是少于2500 --------任何帮助
我试过这个：
I have 4 tables below:
1.Department:
id pk | departName | active| createdDate
2.employees
id pk | name | email | active | createddate | Department_id fk | Designation_Id fk
3.designation
id | name active
4.salary
id | salaryIssuedate | amount | deduction | employee_id fk
--------query----first----------
get all employees with maximum deduction from 2012 to 2013
---------query------2nd-----------
get employee registered in 2013 belogns to HRM department and employees salary should be less than 2500--------any help
I tried this :
select Employee.Name as EmployeeName,MAX(Deduction) as MaximumDeduction
from Employee
full join Salary
on Employee.ID=Salary.Employee_ID
where Salary.SalaryIssueDate between '2012'and '2013'
group by Employee.Name
和第二次查询这个-----------------
SELECT员工.Name为EmployeeName，Employee.CreatedDate，Department.DepartmentName，Amount
FROM Employee
INNER JOIN Department
ON Employee.Department_ID = Department。 ID
INNER JOIN工资
ON Employee.ID = Salary.ID
其中DATEPART（YYYY，Employee.CreatedDate）='2013'和Department.DepartmentName ='HRM'和Salary.Amount< 2500
and this for 2nd query-----------------
SELECT Employee.Name as EmployeeName,Employee.CreatedDate,Department.DepartmentName,Amount
FROM Employee
INNER JOIN Department
ON Employee.Department_ID = Department.ID
INNER JOIN Salary
ON Employee.ID = Salary.ID
where DATEPART(YYYY,Employee.CreatedDate) = '2013' and Department.DepartmentName='HRM' and Salary.Amount<2500
推荐答案
select Employee.Name as EmployeeName,MAX(Deduction) as MaximumDeduction
from Employee
left join Salary on Employee.ID=Salary.Employee_ID
where DATEPART(YEAR,Salary.SalaryIssueDate) between 2012 and 2013
group by Employee.Name
第二次查询检查这个
For second query check this
select Employee.Name as EmployeeName
from Employee
inner join Department on Employee.Department_id=Department.id
inner join
(
select Employee_ID,MAX(salaryIssuedate),amount
from Salary
group by Employee_ID
) sal on sal.Employee_ID=Employee.ID
where DATEPART(YEAR,Employee.createddate) = 2013 and Department.departName='HRM' and sal.amount<2500
group by Employee.Name
注意：我没有在SSMS中执行这些操作
希望，它有帮助：）
Note: I haven't executed these in SSMS
Hope, it helps :)
这篇关于sql加入查询sql server 2008 r2的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！