Oracle错误:SELECT列表与GROUP BY不一致

Question

我是SQL的新手，我正在努力理解这个挑战：SQL竞赛

我在挑战的讨论线程中找到了这个解决方案：

SELECT submission_date, 
(SELECT COUNT(distinct hacker_id)  
FROM Submissions s2  
WHERE s2.submission_date = s1.submission_date 
AND (SELECT COUNT(distinct s3.submission_date) 
FROM Submissions s3 WHERE s3.hacker_id = s2.hacker_id AND s3.submission_date < s1.submission_date) 
= (s1.submission_date - TO_DATE('2016-03-01'))),
(SELECT hacker_id from submissions s2 where s2.submission_date = s1.submission_date 
GROUP BY hacker_id 
ORDER BY count(submission_id) desc
FETCH FIRST 1 ROW ONLY) as shit,
(SELECT hacker_name from hackers where hacker_id = shit)
FROM 
(SELECT distinct submission_date from submissions) s1
group by submission_date;

正如标题所说，我得到的错误是"；SELECT列表与GROUP BY不一致"；我不明白。如果我试图运行脚本，我会得到一个我也不理解的错误：

命令行错误：12列：56

错误报告-SQL错误：

ORA-00904："；SHIT"：标识符无效

904. 00000-"%s： 无效标识符">

*原因：

*行动：

Answer 1

如果没有表，我们就无法运行该代码。

不管怎样，你提到的错误是在这一行中提出的：

(SELECT hacker_name from hackers where hacker_id = shit)
----

因为不能引用属于同一select语句的列。例如，您可以使用子查询或CTE来收集垃圾，然后重用它。类似这样的东西(简化(：

with 
-- you need S1 because you use it in A_CTE (see the "s2 join s1" part)
s1 as
(SELECT distinct submission_date from submissions),
a_cte as
(SELECT hacker_id as shit
from submissions s2 join s1 on s2.submission_date = s1.submission_date   --> S1 is used here
GROUP BY hacker_id 
ORDER BY count(submission_id) desc
FETCH FIRST 1 ROW ONLY
)
-- finally, you can now join HACKERS to A_CTE and use that SHIT
select hacker_name
from hackers h join a_cte on h.hacker_id = a.shit