如何统计2022-2023年活跃员工总数？解决每周记录重复计数问题

你的当前SQL逻辑存在问题：group by PI.PersonId会将每个员工拆分为单独分组，count(*)统计的是该员工的周记录条数，再加distinct仅能去重这些条数，完全无法实现“每个员工当年仅计数一次”的需求。

单年份统计（以2022年为例） #

直接使用count(distinct PI.PersonId)即可自动对员工ID去重，不管该员工有多少周的活跃记录，仅计数一次：

select count(distinct PI.PersonId) as 活跃员工总数
from TimeSheetsView TSV
inner join Person_Identification PI on PI.PersonId = TSV.Personid
inner join Order_Person_Detail_Record OPDR on OPDR.PersonId = Pi.PersonId 
    and OPDR.DetailRecId = TSV.DetailRecId 
    and OPDR.OrderId = TSV.orderid
where PI.PersonType = 'AS' 
    and tsv.recordtype = 'A' 
    and left(yearweek,4) = '2022'

同时统计2022、2023年数据 #

按年份分组，即可一次性获取两年的活跃员工数：

select left(yearweek,4) as 年份,
       count(distinct PI.PersonId) as 活跃员工总数
from TimeSheetsView TSV
inner join Person_Identification PI on PI.PersonId = TSV.Personid
inner join Order_Person_Detail_Record OPDR on OPDR.PersonId = Pi.PersonId 
    and OPDR.DetailRecId = TSV.DetailRecId 
    and OPDR.OrderId = TSV.orderid
where PI.PersonType = 'AS' 
    and tsv.recordtype = 'A' 
    and left(yearweek,4) in ('2022','2023')
group by left(yearweek,4)
order by 年份

备选方案（子查询方式） #

先通过子查询筛选出各年份下的唯一员工ID，再外层计数，数据量较大时性能可能更优：

select 年份, count(PersonId) as 活跃员工总数
from (
    select distinct 
        left(yearweek,4) as 年份,
        PI.PersonId
    from TimeSheetsView TSV
    inner join Person_Identification PI on PI.PersonId = TSV.Personid
    inner join Order_Person_Detail_Record OPDR on OPDR.PersonId = Pi.PersonId 
        and OPDR.DetailRecId = TSV.DetailRecId 
        and OPDR.OrderId = TSV.orderid
    where PI.PersonType = 'AS' 
        and tsv.recordtype = 'A' 
        and left(yearweek,4) in ('2022','2023')
) t
group by 年份
order by 年份

内容的提问来源于stack exchange，提问作者jin