使用FUNCTION-SQL Server查询来查找员工的当前职位

Question

你能告诉我们如何从表中找到员工最后更新的帖子吗。两个表：Employee和EmployeeDetails。Employee字段为：EmployeeID，EmployeeName EmployeeDetails字段为：雇员ID，职务，晋升日期。

如果我们提供EmployeeID，如何查找员工的当前职务。使用函数。我想创建一个简单的函数，使用EmployeeID根据当前的Designation(Latest PromotionDate(获取Designation，然后与Employee表连接。

DROP TABLE IF EXISTS Employee
CREATE TABLE Employee
(
EmployeeID INT NOT NULL PRIMARY KEY IDENTITY(1000,1),
EmployeeName VARCHAR(25)
)
INSERT INTO Employee VALUES('AAA');
INSERT INTO Employee VALUES('LAAA');
INSERT INTO Employee VALUES('RSSS');
INSERT INTO Employee VALUES('SEEE');
INSERT INTO Employee VALUES('CFFF');
INSERT INTO Employee VALUES('SEEEW');
INSERT INTO Employee VALUES('MCCC');
INSERT INTO Employee VALUES('DERR');
INSERT INTO Employee VALUES('DERR');
INSERT INTO Employee VALUES('DERW');
SELECT * FROM Employee
DROP TABLE EmployeeDetails
CREATE TABLE EmployeeDetails
(
EmployeeID INT FOREIGN KEY REFERENCES Employee(EmployeeID),
Designation VARCHAR(25),
PromotionDate Date
)
INSERT INTO EmployeeDetails VALUES(1000,'www','2020-11-20');
INSERT INTO EmployeeDetails VALUES(1000,'qqq','2020-01-23');
INSERT INTO EmployeeDetails VALUES(1009,'qqq','2020-09-20');

SELECT * FROM  EmployeeDetails
SELECT
E.EmployeeID,
E.EmployeeName,
ED.Designation, ED.PromotionDate
FROM 
Employee E 
JOIN  
EmployeeDetails ED ON E.EmployeeID = ED.EmployeeID

我为此写了一个函数，但我不知道如何将其与查询结合起来：

CREATE FUNCTION GetOnlyTheCurrentPost
( @EmpID INT)
RETURNS DATE
AS
BEGIN
DECLARE @PromoDate DATE
SELECT @PromoDate= MAX(PromotionDate)
FROM EmployeeDetails
WHERE EmployeeID = @EmpID
RETURN(@PromoDate)
END

我更改了如下的功能

ALTER FUNCTION [dbo].[GetOnlyTheCurrentPost]
( @PromoDate DATE)
RETURNS DATE 
AS
BEGIN
SELECT  @PromoDate= MAX(PromotionDate)
FROM EmployeeDetails 
RETURN(@PromoDate)
END
SELECT
E.EmployeeID,
ED.Designation,[dbo].[GetOnlyTheCurrentPost](ED.PromotionDate) AS LatestPost
FROM
Employee E
JOIN
EmployeeDetails ED
ON E.EmployeeID = ED.EmployeeID

这将显示所有记录，不仅显示最新的帖子，还显示每条记录。

我再次改变了我的功能。如果我给EmployeeID Like，SELECT [dbo].[GetOnlyTheCurrentDesignation](1011)，我想获得当前的职位。输出应根据给定的相应EmployeeID输出打印：ProjectManager

ALTER FUNCTION GetOnlyTheCurrentDesignation
(@EmpID INT)
RETURNS VARCHAR(MAX) 
AS
BEGIN
DECLARE @Designation VARCHAR(25)
SELECT @Designation=Designation, MAX(PromotionDate)
FROM EmployeeDetails
WHERE EmployeeID = @EmpID
RETURN(@Designation)
END

请告诉我一个解决方案来修复这个

Answer 1

试试这样的东西：

SELECT
E.EmployeeID,
E.EmployeeName,
ED.Designation, ED.PromotionDate
FROM 
Employee E 
JOIN  
(SELECT * FROM EmployeeDetails ED2
WHERE PromotionDate = (SELECT MAX(PromotionDate) 
FROM EmployeeDetails 
WHERE EmployeeID = ED2.EmployeeID)) ED 
ON E.EmployeeID = ED.EmployeeID