时间戳

首页 > 解决方案 > SQL 查询具有最后结束日期的非活动用户

sql - SQL 查询具有最后结束日期的非活动用户

问题描述

这是我在一年前提出的一个问题的后续旧线程 当时我得到的答案运行良好，但现在我发现我需要调整查询才能获得非活动用户的最新结束日期。

所以这里有一个用户的快速示例表，有些是活跃的，有些是不活跃的，有些有几个工作时期。

当某人重新就业时，将为该就业期间添加一个新行。用户名将始终相同。

因此，如果有多个工作期，我想找出哪些用户被禁用并且没有积极的工作，我想要一个具有最晚结束日期的用户。每个用户名一行，包含所有列。

数据库是 SQL Server 2016。

示例表：

|  username | name       | active | Job title   | enddate 
+-----------+----------- +--------+-------------+----------
| 1111      | Jane Doe   |    1   | CIO         | 1/3/2022
| 1111      | Jane Doe   |    0   | Janitor     | 1/2/2018
| 1112      | Bob Doe    |    1   | Coder       | NULL
| 1113      | James Doe  |    0   | Coder       | 1/3/2018
| 1114      | Ray Doe    |    1   | Manager     | NULL
| 1114      | Ray Doe    |    0   | Clerk       | 2/2/2019
| 1115      | Emma Doe   |    1   | Waiter      | NULL
| 1116      | Sarah Doe  |    0   | Greeter     | 3/4/2016
| 1116      | Sarah Doe  |    0   | Trainer     | 4/5/2019

因此，对于用户 1116，我理想情况下会得到一行 enddate 4/5/2019

我从旧线程的答案中使用的查询是这个：

    ;WITH NonActiveDisabledUsers AS
(
    SELECT DISTINCT
        U.username
    FROM
        UserEmployment AS U
    WHERE
        U.active = 0 AND
        NOT EXISTS (SELECT 'no current active employment' 
                    FROM UserEmployment AS C
                    WHERE U.username = C.username AND 
                    C.active = 1 AND
                    (C.enddate IS NULL OR C.enddate >= CONVERT(DATE, GETDATE())))
)
SELECT
    R.*
FROM
    NonActiveDisabledUsers AS N
    CROSS APPLY (
        SELECT TOP 1        -- Just 1 record
            U.*
        FROM
            UserEmployment AS U
        WHERE
            N.username = U.username AND
            U.active = 0
        ORDER BY
            U.enddate DESC  -- Determine which record should we display
        ) AS R

这为我提供了正确的用户和就业状态，但不是最新的结束日期，因为它将为用户 1116 获得第一个结果

标签： sqlsql-serverdatabase

解决方案

我们可以使用条件聚合和窗口聚合来获取该用户的活动行数。

然后我们过滤到仅非活动状态，并通过enddate获取每组的第一行来对结果进行行号：

SELECT
    username,
    name,
    active,
    [Job title],
    enddate 
FROM (
    SELECT *, rn = ROW_NUMBER() OVER (PARTITION BY username ORDER BY enddate DESC)
    FROM (
        SELECT *,
          CountOfActive = COUNT(CASE WHEN
                Active = 1 AND
                (enddate IS NULL OR enddate >= CONVERT(DATE, GETDATE())) THEN 1 END
              ) OVER (PARTITION BY username)
        FROM UserEmployment
    ) AS t
    WHERE CountOfActive = 0
) AS t
WHERE rn = 1;

请注意，行编号不考虑enddate最后排序的空值。你需要一个条件排序：

ROW_NUMBER() OVER (PARTITION BY username ORDER BY CASE WHEN enddate IS NULL THEN 0 ELSE 1 END ASC, enddate DESC)

推荐阅读