select语句的SQl计数()帮助

Question

我用的是这个代码：

  SELECT MACH_NO, 
         COUNT(MACH_NO) AS TOTAL_REPORTS
    FROM MAINTENANCE_LOG
GROUP BY MACH_NO;

...to检索一些数据，这些数据提供：

MACH_NO  TOTAL_REPORTS
----------------------
1        4
5        2
8        1
7        1

在报告总数大于3的情况下，我如何检索？我试过：

WHERE TOTAL_REPORTS > 3 

...but，它说

ORA-00904："TOTAL_REPORTS"：无效标识符

Answer 1

既然您已经使用了GROUP BY，那么您应该使用HAVING而不是WHERE。您还需要显式地使用COUNT(MACH_NO)而不是TOTAL_REPORTS的别名。

所以使用HAVING COUNT(MACH_NO) > 3，而不是WHERE TOTAL_REPORTS > 3。

SELECT MACH_NO, 
       COUNT(MACH_NO) AS TOTAL_REPORTS 
FROM MAINTENANCE_LOG 
GROUP BY MACH_NO
HAVING COUNT(MACH_NO) > 3;

Answer 2

使用有子句

SELECT MACH_NO, COUNT(MACH_NO) AS TOTAL_REPORTS FROM MAINTENANCE_LOG GROUP BY MACH_NO HAVING TOTAL_REPORTS > 3;

Answer 3

选择MACH_NO，计数( MACH_NO )为TOTAL_REPORTS，从MAINTENANCE_LOG组中选择MACH_NO having count(mach_no) > 3