Oracle报错：SELECT列表与GROUP BY不一致及ORA-00904问题求助

问题背景 #

我是SQL新手，正在完成Hackerrank上的《15-days-of-learning-sql》挑战。从论坛找到的解决方案脚本运行时出现两个错误：SELECT list inconsistent with GROUP BY 和 ORA-00904: "SHIT": invalid identifier，以下是错误原因和修正方案：

原脚本：

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;

错误1：SELECT list inconsistent with GROUP BY #

原因：主查询的s1已经是去重后的日期集合（SELECT distinct submission_date from submissions），每个日期只会出现一次，此时再执行GROUP BY submission_date完全多余。Oracle的GROUP BY规则要求SELECT列表中的所有非聚合列必须出现在GROUP BY子句中，多余的GROUP BY会触发语法校验错误。

解决：直接去掉主查询末尾的group by submission_date。

错误2：ORA-00904: "SHIT": invalid identifier #

原因：Oracle不允许在同一个SELECT列表中，用列别名（这里的shit）直接引用其他表达式的结果。也就是说，你不能在SELECT里定义as shit后，紧接着在后面的子查询里用shit这个别名。

解决：把获取hacker_id和hacker_name的逻辑合并成关联子查询，或者拆分独立子查询。同时加上hacker_id排序，处理同一天多个黑客提交数相同的情况（符合题目选id最小的要求）：

将原脚本中获取hacker_id和hacker_name的部分替换为两个独立子查询，避免别名引用问题：

(SELECT s2.hacker_id 
 FROM submissions s2 
 WHERE s2.submission_date = s1.submission_date 
 GROUP BY s2.hacker_id 
 ORDER BY count(submission_id) desc, s2.hacker_id asc
 FETCH FIRST 1 ROW ONLY) as top_hacker_id,
(SELECT h.hacker_name 
 FROM submissions s2 
 JOIN hackers h ON s2.hacker_id = h.hacker_id
 WHERE s2.submission_date = s1.submission_date 
 GROUP BY s2.hacker_id, h.hacker_name 
 ORDER BY count(submission_id) desc, s2.hacker_id asc
 FETCH FIRST 1 ROW ONLY) as top_hacker_name

修正后的完整脚本 #

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 s2.hacker_id 
 FROM submissions s2 
 WHERE s2.submission_date = s1.submission_date 
 GROUP BY s2.hacker_id 
 ORDER BY count(submission_id) desc, s2.hacker_id asc
 FETCH FIRST 1 ROW ONLY) as top_hacker_id,
(SELECT h.hacker_name 
 FROM submissions s2 
 JOIN hackers h ON s2.hacker_id = h.hacker_id
 WHERE s2.submission_date = s1.submission_date 
 GROUP BY s2.hacker_id, h.hacker_name 
 ORDER BY count(submission_id) desc, s2.hacker_id asc
 FETCH FIRST 1 ROW ONLY) as top_hacker_name
FROM 
(SELECT distinct submission_date from submissions) s1;

内容的提问来源于stack exchange，提问作者user15603244