时间戳

首页 > 解决方案 > 使用 FUNCTION -SQL Server 查询查找员工的当前职位

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

问题描述

你能告诉如何从表中找到员工的最后更新帖子吗？两个表：Employee 和 EmployeeDetails。员工字段为：EmployeeID、EmployeeName EmployeeDetails 字段为：EmployeeID、Designation、PromotionDate。

如果我们提供 EmployeeID，如何找到员工的当前名称。使用功能。我想创建一个简单的函数来根据当前的 Designation (Latest PromotionDate ) 使用 EmployeeID 获取 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

请告诉我解决此问题的解决方案

标签： sqlsql-serverdatabasefunction

解决方案

尝试这样的事情：

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

推荐阅读