sql - Oracle SQL:如何返回几个不同的列
问题描述
我有一个输出的 Oracle 查询
但我希望输出除事件 ID 之外的所有不同列,即
我尝试将 GROUP BY 添加到 Oracle Query 但出现错误,
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
以下是我的查询...请协助
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
解决方案
修复JOIN
语法并使用聚合:
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 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 I.CREATIONDATE, MO.IPADDRESS, MO.DOMAINUSERNAME, MO.ENDPOINTMACHINENAME, M.MESSAGESUBJECT
所有未聚合的列都需要在GROUP BY
.
为什么源和日期必须匹配加入INCIDENT
以及消息 ID?似乎消息 id 应该足够了。
推荐阅读
- python-3.x - 无法导入名称“LoggingConfigurable”
- laravel - 在 Laravel 应用程序中获取 SSL 证书已过期错误,但它应该是有效的
- node.js - 不能在 Windows 上使用 expo 和 expo-cli
- flutter - 使用不包含 UserBloc 类型的块的上下文调用错误 BlocProvider.of()
- angular - 我必须在 Angular 生产构建之前修复所有 TS 错误吗?
- list - Prolog - 如何计算列表的元素?
- java - 杰克逊序列化:包含正则表达式的字符串字段反转义以获取实际文字
- linux - 以编程方式获取容器内的 linux 进程内存使用情况
- python-3.x - evernote.edam.userstore.UserStore.Client:HTTP 状态 400
- eclipse - 如何使用 xtext 制作用于检查源代码的 DSL