代码之家 › 专栏 › 技术社区 › Learning_Learning

T-SQL-基于特定值删除行

sql-server-2014 tsql sql

Learning_Learning · 技术社区 · 8 年前

在我的例子中,我需要根据其用户及其角色过滤文件。

用户可以有两个角色“A”和“B”。基于以下条件,我只希望每一行有一个文件。

If
Else If StateFile使其用户存在于文件中,并具有角色“B”,然后-->是的,是的
否则,如果 StateFile在文件中不存在其用户,并且没有角色“B”,则-->不,不

SELECT DISTINCT MSEF.StatefileID,
MSEF.StateFileName,
AFEE.SubmittedDate,
UNO.DisplayName AS SubmittedBy,
        (select DISTINCT (
    CASE WHEN (
     MSETET.ITSUserID = '99' 
                 AND ET.StateTeamName = N'Live')
            THEN 'Yes'
ELSE 'No'
    END)) AS CHICMAN,
    (select DISTINCT (
    CASE WHEN ( 

    MSETET.ITSUserID  IN( '99') 
          AND ET.StateTeamName  IN( N'Live') 
          AND MSETET.StateTeamID=ET.StateTeamID ) --AND MSEF.StateFileID != MSETET.StateFileID    
                 THEN 'Yes'
ELSE 'No'
    END)) AS CHICTeamIsInTheLive  
from MS.StateFile MSEF WITH (NOLOCK)
INNER JOIN EM.StateFileRem AFEE WITH (NOLOCK) ON MSEF.StateFileID = AFEE.StateFileID
INNER JOIN MS.StateTeamMember MSETM WITH (NOLOCK) ON MSETM.StateFileID=MSEF.StateFileID
INNER JOIN SOP.ITSUser UNO WITH (NOLOCK) ON AFEE.SubmittedByUserID = UNO.ITSUserID
INNER JOIN MS.StateTeamStateTeamMember MSETET ON MSETET.StateFileID=AFEE.StateFileID
INNER JOIN [MS].[StateTeam] ET WITH (NOLOCK) ON ET.StateFileID = MSETET.StateFileID
 ORDER BY
    MSEF.StateFileID 
GO

我的电流输出:

1 回复 | 直到 8 年前

Raphael Müllner 8 年前

这应该有效:

select top 1 with ties
    StatefileID
    ,StateFileName
    ,SubmittedDate
    ,SubmittedBy
    ,CHICMAN
    ,CHICTeamIsInTheLive
from 
(
    SELECT DISTINCT MSEF.StatefileID,
    MSEF.StateFileName,
    AFEE.SubmittedDate,
    UNO.DisplayName AS SubmittedBy,
            (select DISTINCT (
        CASE WHEN (
         MSETET.ITSUserID = '99' 
                     AND ET.StateTeamName = N'Live')
                THEN 'Yes'
    ELSE 'No'
        END)) AS CHICMAN,
        (select DISTINCT (
        CASE WHEN ( 

        MSETET.ITSUserID  IN( '99') 
              AND ET.StateTeamName  IN( N'Live') 
              AND MSETET.StateTeamID=ET.StateTeamID ) --AND MSEF.StateFileID != MSETET.StateFileID    
                     THEN 'Yes'
    ELSE 'No'
        END)) AS CHICTeamIsInTheLive  
    from MS.StateFile MSEF WITH (NOLOCK)
    INNER JOIN EM.StateFileRem AFEE WITH (NOLOCK) ON MSEF.StateFileID = AFEE.StateFileID
    INNER JOIN MS.StateTeamMember MSETM WITH (NOLOCK) ON MSETM.StateFileID=MSEF.StateFileID
    INNER JOIN SOP.ITSUser UNO WITH (NOLOCK) ON AFEE.SubmittedByUserID = UNO.ITSUserID
    INNER JOIN MS.StateTeamStateTeamMember MSETET ON MSETET.StateFileID=AFEE.StateFileID
    INNER JOIN [MS].[StateTeam] ET WITH (NOLOCK) ON ET.StateFileID = MSETET.StateFileID
) x
where CHICMAN = 'Yes' or CHICTeamIsInTheLive = 'Yes'
order by row_number() over (partition by StatefileID order by CHICMAN desc, CHICTeamIsInTheLive desc)