SQL Server 查找值更改的时间

xqk2d5yq 于 2023-02-03 发布在其他

关注(0)|答案(1)|浏览(109)

我还在学习许多关于SQL的新东西，如PARTITION BY和CTE。我目前正在处理一个查询，我从网上找到的一个类似的问题拼凑而成。但是，我似乎无法让它按预期工作。
问题如下--我的任务是显示一个组织从2022年初到今天的职级晋升。我正在使用两个主表，一个EMPLOYEES表和一个PERIODS表。此periods表捕获每个月任何给定雇员的快照-包括他们当时的级别。每个月还分配有一个PeriodID（例如Jan 2022 = PeriodID 131）。我们的EMPLOYEE表保存员工的当前排名。这些排名以整数形式存储（例如1、2、3，其中1为最低排名）。员工在任何给定月份都有可能多次排名上升。
为了解决这个问题，我已经尽可能地简化了使用的查询。查询如下所示：

;WITH x AS
(
SELECT 
e.EmployeeID, p.PeriodID, p.RankID,
rn = ROW_NUMBER() OVER (PARTITION BY e.EmployeeID ORDER BY p.PeriodID DESC)
FROM employees e
LEFT JOIN periods p on p.EmployeeID= e.EmployeeID
WHERE p.PeriodID <= 131 AND p.PeriodID >=118 --This is the time range mentioned above
),

rest AS (SELECT * FROM x  WHERE rn > 1)

SELECT 
main.EmployeeID,

PeriodID = MIN(
CASE
WHEN main.CurrentRankID = Rest.RankID
THEN rest.PeriodID ELSE main.PeriodID
END),

main.RankID, rest.RankID
FROM x AS main LEFT OUTER JOIN rest ON main.EmployeeID = rest.EmployeeID 
AND rest.rn >1
LEFT JOIN periods p on p.EmployeeID = e.EmployeeID
WHERE main.rn = 1
AND NOT EXISTS
(
SELECT 1 FROM rest AS rest2 
WHERE EmployeeID = rest.EmployeeID
AND rn < rest.rn
AND main.RankID <> rest.RankID
)
and p.PeriodID <= 131 AND p.PeriodID >=118

GROUP BY main.EmployeeID, main.PeriodID, main.RankID, rest.RankID

如前所述，这个查询是从一个类似的问题中借用的，并为我自己的使用进行了修改。我想查询的 backbone 是好的，也许我在某个地方搞乱了一个变量，但我似乎找不到问题所在。最终目标是查询产生一个表，显示EmployeeID，PeriodID，他们正在晋升的级别，以及他们在获得晋升的当月晋升到的级别。与下面的内容类似。
| 员工ID|周期ID|渗透等级ID|新等级|
| - ------|- ------|- ------|- ------|
| 一百二十三|一百三十一|1个|第二章|
| 一百二十三|一百三十三|第二章|三个|
相反，我的查询会重复显示以前/当前的排名，并且PeriodID似乎是静态的（如下图所示）。
| 员工ID|周期ID|渗透等级ID|新等级|
| - ------|- ------|- ------|- ------|
| 一百二十三|一百三十一|1个|1个|
| 一百二十三|一百三十一|1个|1个|
我希望有一个对这些函数有更大知识基础的人能够很快注意到我的错误。

sql-server

来源：https://stackoverflow.com/questions/75299838/finding-a-time-when-a-value-changed

1条答案

按热度按时间

wljmcqd81#

如果我们假设一些示例DML/DDL（在您的问题中提供这些示例非常有帮助）：

DECLARE @Employees TABLE (EmployeeID INT IDENTITY, Name VARCHAR(20), RankID INT);
DECLARE @Periods TABLE (PeriodID INT, EmployeeID INT, RankID INT);

INSERT INTO @Employees (Name, RankID) VALUES ('Jonathan', 10),('Christopher', 10),('James', 10),('Jean-Luc', 8);
INSERT INTO @Periods (PeriodID, EmployeeID, RankID) VALUES 
(1,1,1),(2,1,1),(3,1,1),(4,1,8 ),(5,1,10),(6,1,10),
(1,2,1),(2,2,1),(3,2,1),(4,2,8 ),(5,2,8 ),(6,2,10),
(1,3,1),(2,3,1),(3,3,7),(4,3,10),(5,3,10),(6,3,10),
(1,4,1),(2,4,1),(3,4,1),(4,4,8 ),(5,4,9 ),(6,4,9 )

然后，我们可以使用OUTER APPLY来完成我认为您正在寻找的内容，然后基于当前行的值来聚合值：

SELECT e.EmployeeID, e.Name, e.RankID AS CurrentRank, ap.PeriodID AS ThisPeriod, p.PeriodID AS LastRankChangePeriodID, p.RankID AS LastRankChangedFrom, ap.RankID - p.RankID AS LastRankChanged
  FROM @Employees e
    LEFT OUTER JOIN @Periods ap
      ON e.EmployeeID = ap.EmployeeID
    OUTER APPLY (
                 SELECT EmployeeID, MAX(PeriodID) AS PeriodID
                   FROM @Periods
                  WHERE EmployeeID = e.EmployeeID
                    AND RankID <> ap.RankID
                    AND PeriodID < ap.PeriodID
                  GROUP BY EmployeeID
                ) a
    LEFT OUTER JOIN @Periods p
      ON a.EmployeeID = p.EmployeeID
      AND a.PeriodID = p.PeriodID
 ORDER BY e.EmployeeID, ap.PeriodID DESC

使用相关子查询，我们可以得到一个数据视图，可以使用当前行的值对该视图进行过滤，然后聚合该视图以返回我们要查找的期间（该期间在此期间之前，并且排名不同），然后只需连接回Periods表以获取值。
你使用了一个左连接，所以我使用了一个外部应用来保留它。如果你想用它来过滤，它将是一个交叉应用。
| 员工ID|姓名|当前排名|本期|最后等级更改周期ID|上次等级更改自|上次更改的等级|
| - ------|- ------|- ------|- ------|- ------|- ------|- ------|
| 1个|乔纳森|十个|六个|四个|八个|第二章|
| 1个|乔纳森|十个|五个|四个|八个|第二章|
| 1个|乔纳森|十个|四个|三个|1个|七|
| 1个|乔纳森|十个|三个||||
| 1个|乔纳森|十个|第二章||||
| 1个|乔纳森|十个|1个||||
| 第二章|克里斯托弗|十个|六个|五个|八个|第二章|
| 第二章|克里斯托弗|十个|五个|三个|1个|七|
| 第二章|克里斯托弗|十个|四个|三个|1个|七|
| 第二章|克里斯托弗|十个|三个||||
| 第二章|克里斯托弗|十个|第二章||||
| 第二章|克里斯托弗|十个|1个||||
| 三个|詹姆斯|十个|六个|三个|七|三个|
| 三个|詹姆斯|十个|五个|三个|七|三个|
| 三个|詹姆斯|十个|四个|三个|七|三个|
| 三个|詹姆斯|十个|三个|第二章|1个|六个|
| 三个|詹姆斯|十个|第二章||||
| 三个|詹姆斯|十个|1个||||
| 四个|让-吕克|八个|六个|五个|九|-1人|
| 四个|让-吕克|八个|五个|四个|八个|1个|
| 四个|让-吕克|八个|四个|三个|1个|七|
| 四个|让-吕克|八个|三个||||
| 四个|让-吕克|八个|第二章||||
| 四个|让-吕克|八个|1个||||
现在，我们可以看到每个时段上一次更改的情况。当前Jonathan的RankID为10。上一次发生变化是在PeriodID 4，当时为8。PeriodID 5也是如此。在PeriodID 4中，他的RankID为8，在此之前，他的RankID为1。在此之前，他的RankID没有更改。
Jean-Luc实际上被降级了，这是他最后一次修改。我不知道这在你的模型中是否可能。

赞(0）回复(0）举报 2023-02-03

我来回答

SQL Server 查找值更改的时间

1条答案

相关问题

热门标签

最新问答