Oracle SQL:如何返回几个不同的列 [英] Oracle SQL: How return several distinct columns

问题描述

我有一个输出的Oracle查询

I have an Oracle Query that outputs

但是我希望输出除事件ID(即

but I wish to output all distinct column except for Incident ID, i.e.

我尝试将GROUP BY添加到Oracle Query，但出现错误，

I tried adding GROUP BY to Oracle Query but get error,

DataSource.Error: Oracle: ORA-00979: not a GROUP BY expression
Details:
    DataSourceKind=Oracle
    DataSourcePath=dlporacle
    Message=ORA-00979: not a GROUP BY expression
    ErrorCode=-2147467259

以下是我的查询...请协助

Below is my Query ... please assist

SELECT 
   I.INCIDENTID                    AS "Incident ID",
   I.CREATIONDATE                  AS "Creation Date",
   MO.IPADDRESS           AS "IP Address",
   MO.DOMAINUSERNAME               AS "Login ID",
   MO.ENDPOINTMACHINENAME          AS "Computer Name",
   M.MESSAGESUBJECT                AS "Email Subject"
 FROM MESSAGE M,
 JOIN INCIDENT I ON M.MESSAGEID = I.MESSAGEID AND M.MESSAGESOURCE = I.MESSAGESOURCE AND  M.MESSAGEDATE  = I.MESSAGEDATE
 JOIN MESSAGEORIGINATOR MO ON  M.MESSAGEORIGINATORID = MO.MESSAGEORIGINATORID
 WHERE  M.MESSAGESOURCE = 'ENDPOINT'
    AND I.ISDELETED = 0
  GROUP BY MO.IPADDRESS, MO.DOMAINUSERNAME, MO.ENDPOINTMACHINENAME, M.MESSAGESUBJECT

推荐答案

尝试一下

 SELECT MIN(I.INCIDENTID)               AS "Incident ID",
        I.CREATIONDATE                  AS "Creation Date",
        MO.IPADDRESS                    AS "IP Address",
        MO.DOMAINUSERNAME               AS "Login ID",
        MO.ENDPOINTMACHINENAME          AS "Computer Name",
        M.MESSAGESUBJECT                AS "Email Subject"
   FROM MESSAGE M,
        INCIDENT I,
        MESSAGEORIGINATOR MO
  WHERE M.MESSAGESOURCE = 'ENDPOINT'
    AND M.MESSAGESOURCE = I.MESSAGESOURCE
    AND M.MESSAGEID = I.MESSAGEID
    AND M.MESSAGEDATE = I.MESSAGEDATE 
    AND M.MESSAGEORIGINATORID = MO.MESSAGEORIGINATORID 
    AND I.ISDELETED = 0
  GROUP BY I.CREATIONDATE, MO.IPADDRESS, MO.DOMAINUSERNAME, MO.ENDPOINTMACHINENAME,M.MESSAGESUBJECT

这篇关于Oracle SQL:如何返回几个不同的列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！