我有下一个查询:
SELECT NAME_TABLE.USER,
TO_CHAR(NAME_TABLE.LAST_LOGIN, 'DD/MM/YYYY') LAST_LOGIN,
NAME_TABLE.SAILA,
MAX(NAME_TABLE.INTENTS) INTENTS,
NAME_TABLE.MAQ
FROM OWNER.MY_TABLE_NAME NAME_TABLE
WHERE NAME_TABLE.LAST_LOGIN>=TO_DATE('22/02/2017' || ' 00:00:00', 'dd/mm/yyyy hh24:mi:ss')
AND NAME_TABLE.LAST_LOGIN <= TO_DATE('22/02/2017' || ' 23:59:59', 'dd/mm/yyyy hh24:mi:ss')
GROUP BY NAME_TABLE.USER,
TO_CHAR(NAME_TABLE.LAST_LOGIN,'DD/MM/YYYY'),
NAME_TABLE.SAILA,
NAME_TABLE.INTENTS,
NAME_TABLE.MAQ
ORDER BY NAME_TABLE.SAILA,
NAME_TABLE.LAST_LOGIN DESC;
我得到下一个错误:
ORA-00979: not a GROUP BY expression
但执行下一步:
SELECT NAME_TABLE.USER,
TO_CHAR(NAME_TABLE.LAST_LOGIN, 'DD/MM/YYYY') LAST_LOGIN,
NAME_TABLE.SAILA,
(SELECT MAX(INTENTS)
FROM OWNER.NAME_TABLE), NAME_TABLE.MAQ
FROM OWNER.MY_TABLE_NAME NAME_TABLE
WHERE NAME_TABLE.LAST_LOGIN>=TO_DATE('22/02/2017' || ' 00:00:00', 'dd/mm/yyyy hh24:mi:ss')
AND NAME_TABLE.LAST_LOGIN <= TO_DATE('22/02/2017' || ' 23:59:59', 'dd/mm/yyyy hh24:mi:ss')
GROUP BY NAME_TABLE.USER,
TO_CHAR(NAME_TABLE.LAST_LOGIN,'DD/MM/YYYY'),
NAME_TABLE.SAILA,
NAME_TABLE.INTENTS,
NAME_TABLE.MAQ
ORDER BY NAME_TABLE.SAILA;
任何解决方案?
为什么退出最新字段效果很好?
和remplace
MAX(NAME_TABLE.INTENTS) INTENTS
(SELECT MAX(INTENTS) FROM OWNER.NAME_TABLE)
为什么?
更新
SELECT USER, TO_CHAR(LAST_LOGIN, 'DD/MM/YYYY') LAST_LOGIN, SAILA, (SELECT MAX(INTENTS) FROM OWNER.NAME_TABLE, MAQ FROM OWNER.MY_TABLE_NAME
WHERE LAST_LOGIN>=TO_DATE('22/02/2017' || ' 00:00:00', 'dd/mm/yyyy hh24:mi:ss') AND LAST_LOGIN <= TO_DATE('22/02/2017' || ' 23:59:59', 'dd/mm/yyyy hh24:mi:ss')
GROUP BY USER, TO_CHAR(LAST_LOGIN,'DD/MM/YYYY'), SAILA, INTENTS, MAQ ORDER BY SAILA;
为什么退出name_table.field到字段没有显示错误?
iam noob。
谢谢!
您的问题是ORDER BY
。实际上,您在GROUP BY
中有太多列:
SELECT NAME_TABLE.USER,
TO_CHAR(NAME_TABLE.LAST_LOGIN, 'DD/MM/YYYY') as LAST_LOGIN,
NAME_TABLE.SAILA,
MAX(NAME_TABLE.INTENTS) as INTENTS,
NAME_TABLE.MAQ
FROM OWNER.MY_TABLE_NAME NAME_TABLE
WHERE NAME_TABLE.LAST_LOGIN>=TO_DATE('22/02/2017' || ' 00:00:00', 'dd/mm/yyyy hh24:mi:ss')
AND NAME_TABLE.LAST_LOGIN <= TO_DATE('22/02/2017' || ' 23:59:59', 'dd/mm/yyyy hh24:mi:ss')
GROUP BY NAME_TABLE.USER,
TO_CHAR(NAME_TABLE.LAST_LOGIN,'DD/MM/YYYY'),
NAME_TABLE.SAILA,
NAME_TABLE.MAQ
ORDER BY NAME_TABLE.SAILA,
MAX(NAME_TABLE.LAST_LOGIN) DESC;
so:
-
NAME_TABLE.INTENTS
不应在GROUP BY
中,因为它是MAX()
的论点。 -
NAME_TABLE.LAST_LOGIN
不能在ORDER BY
中,因为它不在GROUP BY
中。您可以放入LAST_LOGIN
中,但是列的定义是订购尴尬的方式。因此,MAX()
解决了问题。 - 我建议将
WHERE
中的条件更改为:NAME_TABLE.LAST_LOGIN >= DATE '2017-02-22' AND NAME_TABLE.LAST_LOGIN < DATE '2017-02-23
。阅读得多。