Linq中的SQL重复行查询

Question

我使用下面的sql查询来计算表MachineLog中的重复项。这个查询是我使用ExecuteSqlCommand在项目中直接执行的。但执行时间超过1分钟。

select Count(*) from MachineLog where Id in
    (
    select Id from (
    select *,
    RANK() OVER ( PARTITION BY CompanyId, DeviceEnrollNo, InOutDate, InOutTime, MachineNo ORDER BY Id DESC )rank
    From MachineLog
    )T
    where rank>1
    )

有人能帮帮忙吗？

Answer 1

如果你只是想计数，试试这个，你为什么要用*呢？这可能会有帮助：

select Count(*) from MachineLog where Id in
    (
    select Id from (
    select Id,
    RANK() OVER ( PARTITION BY CompanyId, DeviceEnrollNo, InOutDate, InOutTime, MachineNo ORDER BY Id DESC )rank
    From MachineLog
    )T
    where rank>1
    )

Answer 2

尝试使用CTE

;WITH CTE AS ( SELECT，Id RANK() OVER ( PARTITION BY CompanyId，DeviceEnrollNo，InOutDate，InOutTime，MachineNo ORDER BY ID DESC ) AS rank from MachineLog ) SELECT Count(*) From MachineLog m JOIN cte c ON c.id=m.ID WHERE c.rank >1