时间戳

首页 > 解决方案 > 无在职残疾用户的SQL查询

sql - 无在职残疾用户的SQL查询

问题描述

与我们所有的用户和他们的工作有一张桌子。

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

试图找出最好的方法来找出哪些用户被禁用并且没有积极的工作。数据库是 SQL Server 2016。

示例表：

|  username | name      | active | enddate 
+-----------+-----------+--------+----------
| 1111      | Jane Doe  |    1   | 1/3/2022
| 1111      | Jane Doe  |    0   | 1/2/2018
| 1112      | Bob Doe   |    1   | NULL
| 1113      | James Doe |    0   | 1/3/2018
| 1114      | Ray Doe   |    1   | NULL
| 1114      | Ray Doe   |    0   | 2/2/2019
| 1115      | Emma Doe  |    1   | NULL
| 1116      | Sara Doe  |    0   | 3/4/2016
| 1116      | Sara Doe  |    0   | 4/5/2019

理想情况下，即使他们有几份工作已经结束，我也希望每个用户名只获得一行。

所以一个查询会得到一个包含用户 1113 和 1116 的两行的列表。

标签： sqlsql-server

解决方案

与这个答案相比，我发现其他答案很难阅读（或没有给出你所要求的）：

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())))

但这是个人口味。这将搜索今天没有任何活动记录的所有禁用用户。

只需确保您的enddate列实际上是DATE而不是VARCHAR，因为它可能会给您带来转换问题。

如果您想显示所有列，但每个用户名显示一次，那么我们将不得不重新加入禁用记录。CROSS APPLY我们可以使用with TOP N+来控制行数ORDER BY：

;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

推荐阅读