确定对每个项目执行的最后一个操作是安装还是删除

Question

我正在尝试规范化一个工作数据库，目前其中一个表大约有 120 列，我正在构建一个新表来纠正许多问题。

我正在尝试弄清楚如何编写一个查询来找出仍在安装哪些面板。

在表中的字段中，一些最相关的字段是：

ActualCompleteDate
PanelID 
WorkType ("electrical install", "electrical removal")

我需要编写一个查询来隔离所有已安装的面板，并允许我获取它们的 ID，以便我可以使用该查询将所有必要的数据迁移到新表中。还有 12 列需要移动到新表中。

编辑

基于以下FuzzyTree解决方案的完整查询：

SELECT  CHSRNumber,CableSize,CableLength,[CEA Type],CHSRLoc
        ,PanelID,ConnectorType,BreakerAmps,Voltage,[Status]
        ,InstallRDCLoc,Phase,UPS,BreakerType,ActualCompleteDate 
FROM (
    SELECT  CHSRNumber,CableSize,CableLength,[CEA Type],CHSRLoc
            ,PanelID,ConnectorType,BreakerAmps,Voltage,[Status]
            ,InstallRDCLoc,Phase,UPS,BreakerType,WorkType,[ActualCompleteDate]
            ,row_number() OVER (
                PARTITION BY PanelID 
                ORDER BY ActualCompleteDate DESC
            ) rn 
    FROM    [Facilities_Database].[Facilities].[HardwareSupportRequest] t1
) t1 
WHERE rn = 1 AND [WorkType] LIKE '%electric install%' 
    --AND (CableLength IS NOT NULL
    --  OR [CEA Type] IS NOT NULL 
    --  OR [CableSize] IS NOT NULL)
ORDER BY CHSRNumber

Answer 1

这将

选择已安装且安装后尚未删除的所有面板ID

select PanelID from (
    select PanelID,
    row_number() over (partition by PanelID order by ActualCompleteDate desc) rn,
    WorkType
    from mytable
) t1 where rn = 1 and WorkType = "electrical install"

或者如果您的数据库不支持 row_number(( 则使用不存在

select PanelID from mytable t1
where WorkType = "electrical install"
and not exists (
    select 1 from mytable t2
    where t2.PanelID = t1.PanelID
    and t2.ActualCompleteDate > t1.ActualCompleteDate
    and t2.WorkType = "electrical removal"
)

Answer 2

另一种方法，更容易调试/扩展：

select i.* from
(select
 PanelID, 
 max(ActualCompleteDate) as max_datei
from 
 table
where
 worktype = "electrical install"
group by
 PanelID) i left outer join
(select
 PanelID, 
 max(ActualCompleteDate) as max_dater 
from 
 table
where
 worktype = "electrical removal"
group by
 PanelID) r on 
i.panelid = r.panelid and
maxdatei > maxdater or 
maxdater is null