ORACLE SQL Group By仍会重复 [英] ORACLE SQL Group By STILL giving Duplicates

问题描述

整天都在工作.

以下查询返回大约162,000条记录.其中有少数是重复项.

Following query returns around 162,000 records. Handful of them are duplicates.

是的，我不确定如何解决其他问题.

Really, I'm not sure how else to troubleshoot.

此数据库从我只有READ Access权限的外部数据库中提取.

This database pulls from external database for which I only have READ Access.

例如，如果我有两行

20684 2/26/2019 11:13:04 AM abra 123 abc 6 abra

20690 2/26/2019 11:13:04 AM abra 123 abc 6 abra

我想保留20684 abra 123 abc 6 abra

20684 is the unique I.INCIDENTID that is not included in GROUP BY`

20684 is the uniqueI.INCIDENTIDthat is not included inGROUP BY`

请协助.

   SELECT TO_CHAR(MIN(I.INCIDENTID))      AS "Incident ID",
          I.CREATIONDATE                  AS "Creation Date",
          M.MESSAGESUBJECT                AS "Email Subject",
          MO.IPADDRESS                    AS "IP Address",
          MO.DOMAINUSERNAME               AS "Login ID",
          MO.ENDPOINTMACHINENAME          AS "Computer Name"
   FROM        MESSAGE M 
   LEFT JOIN   INCIDENT I 
   ON          M.MESSAGESOURCE = I.MESSAGESOURCE 
   AND         M.MESSAGEID = I.MESSAGEID
   AND         M.MESSAGEDATE    = I.MESSAGEDATE 
   LEFT JOIN   MESSAGEORIGINATOR MO
   ON          M.MESSAGEORIGINATORID = MO.MESSAGEORIGINATORID
   LEFT JOIN   MESSAGEEXT ME
   ON          ME.MESSAGEID = M.MESSAGEID
   LEFT JOIN   INCIDENTSTATUS S
   ON          S.INCIDENTSTATUSID = I.INCIDENTSTATUSID
   LEFT JOIN   CUSTOMATTRIBUTESRECORD C
   ON          C.CUSTOMATTRIBUTESRECORDID = I.CUSTOMATTRIBUTESRECORDID                    
   GROUP BY    I.CREATIONDATE,
               M.MESSAGESUBJECT,
               MO.IPADDRESS,
               MO.DOMAINUSERNAME,
               MO.ENDPOINTMACHINENAME

推荐答案

如果CREATIONDATE是日期数据类型，则它包含的时间元素可以低至秒.您所寻找的最低事件ID不太可能具有准确的时间.将日期格式化为ISO日期，GROUP BY将忽略时差.

If CREATIONDATE is a date data type it contains the time element down to seconds. It's highly unlikely that the Incident Id's you're looking for the lowest of will have the exact time. Format the date to a ISO Date and the GROUP BY will ignore the time differences.

SELECT TO_CHAR(MIN(I.INCIDENTID))      AS "Incident ID",
          to_char(I.CREATIONDATE, 'yyyy-mm-dd') AS "Creation Date",
          M.MESSAGESUBJECT                AS "Email Subject",
          MO.IPADDRESS                    AS "IP Address",
          MO.DOMAINUSERNAME               AS "Login ID",
          MO.ENDPOINTMACHINENAME          AS "Computer Name"
   FROM        MESSAGE M 
   LEFT JOIN   INCIDENT I 
   ON          M.MESSAGESOURCE = I.MESSAGESOURCE 
   AND         M.MESSAGEID = I.MESSAGEID
   AND         M.MESSAGEDATE    = I.MESSAGEDATE 
   LEFT JOIN   MESSAGEORIGINATOR MO
   ON          M.MESSAGEORIGINATORID = MO.MESSAGEORIGINATORID
   LEFT JOIN   MESSAGEEXT ME
   ON          ME.MESSAGEID = M.MESSAGEID
   LEFT JOIN   INCIDENTSTATUS S
   ON          S.INCIDENTSTATUSID = I.INCIDENTSTATUSID
   LEFT JOIN   CUSTOMATTRIBUTESRECORD C
   ON          C.CUSTOMATTRIBUTESRECORDID = I.CUSTOMATTRIBUTESRECORDID                    
   GROUP BY    to_char(I.CREATIONDATE, 'yyyy-mm-dd') 
               M.MESSAGESUBJECT,
               MO.IPADDRESS,
               MO.DOMAINUSERNAME,
               MO.ENDPOINTMACHINENAME

这篇关于ORACLE SQL Group By仍会重复的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！