试图获得活跃员工的总数

Question

我希望获得2022年和2023年活跃员工总数的计数。我们的表有每周的记录，所以当我提取记录时，它计算了员工52次。我希望有基于查询计数的人是否有一个活跃的记录在一年内，有它只计数一次。我在select语句中包含了distinct，但仍然得到多个计数，并且它不是真正重复的，所以distinct可能不是这里的解决方案。

select distinct count(*)
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')
group by PI.PersonId

Answer 1

你不应该拿"PI"作为别名，因为PI是保留字。只要在COUNT聚合的括号内使用DISTINCT，就不需要分组:

SELECT 
COUNT(DISTINCT PI.PersonId) AS "Total Count"
FROM TimeSheetsView TSV
INNER JOIN Person_Identification P ON P.PersonId=TSV.Personid
INNER JOIN Order_Person_Detail_Record OPDR 
ON OPDR.PersonId=P.PersonId 
AND OPDR.DetailRecId=TSV.DetailRecId 
AND OPDR.OrderId=TSV.orderid
WHERE P.PersonType='AS' 
AND tsv.recordtype='A' 
AND left(yearweek,4) IN ( '2022');

如果您想查看每年的总数，您可以使用:

SELECT 
LEFT(yearweek,4) AS "Year", 
COUNT(DISTINCT PI.PersonId) AS "Total Count"
FROM TimeSheetsView TSV
INNER JOIN Person_Identification P ON P.PersonId=TSV.Personid
INNER JOIN Order_Person_Detail_Record OPDR 
ON OPDR.PersonId=P.PersonId 
AND OPDR.DetailRecId=TSV.DetailRecId 
AND OPDR.OrderId=TSV.orderid
WHERE P.PersonType='AS' AND tsv.recordtype='A'
GROUP BY LEFT(yearweek,4);