mysql - 我的查询重复记录

Question

我正在尝试构建一个查询来获取报告，但是当我提出条件时，我发现 3 列与结果的其余部分重复。

我错过了什么？

列是A.MailTo、A.Text、a.openTIME、a.heldtime

SELECT DISTINCT U.FirstName, 
                U.LastName,
                U.LogonID,
                S.NAME Skillset,
                A.MailTo,A.Text,
                a.openTIME,a.closedtime 
FROM CLS.USERS U join CLS.Skillsets S on U.CCMSID =S.CCMSID
                 join CLS.CONTACTS C on S.ID=C.SKILLSET 
                 join CLS.Actions  A on A.agent=C.agent
WHERE a.source=40 
 and cast(a.closedtime as date)= cast(CURRENT_TIMESTAMP as date)-1 

Answer 1

您似乎对每个联系人执行了很多操作。您可以像这样显示它们的计数(如果 ID 是操作的主键):

SELECT * from (
SELECT      U.FirstName, 
            U.LastName,
            U.LogonID,
            S.NAME Skillset,
            count(A.ID) agentCount
FROM CLS.USERS U join CLS.Skillsets S on U.CCMSID =S.CCMSID
             join CLS.CONTACTS C on S.ID=C.SKILLSET 
             join CLS.Actions  A on A.agent=C.agent
WHERE a.source=40 
and cast(a.closedtime as date)= cast(CURRENT_TIMESTAMP as date)-1
group by   U.FirstName, 
            U.LastName,
            U.LogonID,
            S.NAME
) order by agentCount desc;