SQL Oracle在count中缺少右括号

Question

不知道为什么它一直说缺少右括号，它在(*)处强调了错误。请帮忙，谢谢。

   SELECT FIRST, LAST 
    FROM OFFICERS
    WHERE OFFICER_ID IN (SELECT OFFICER_ID FROM CRIME_OFFICERS COUNT(*) as TotalReportsOfficer                  
    GROUP BY OFFICER_ID
    HAVING TotalReportsOfficer > ((SELECT COUNT(CRIME_ID) from CRIME_OFFICERS))/ (SELECT COUNT(OFFICER_ID) from OFFICERS));

错误:ORA-00907:缺少右括号00907. 00000 - "缺少右括号"*原因:
*动作:Line: 3 Column: 66

更新:我想找出每个警官的报告数量，然后比较它们，找出哪个警官的报告数量大于平均报告数量。

Answer 1

你好，
你可以使用下面的查询，

SELECT OF.FIRST, OF.LAST  FROM OFFICERS OF
    INNER JOIN (SELECT OFFICER_ID, COUNT(*) AS TotalReportsOfficer FROM OFFICERS
    GROUP BY CRIME_OFFICERS) COF
    ON (OF1.OFFICER_ID =COF.OFFICER_ID )
    HAVING COF.TotalReportsOfficer > ((SELECT COUNT(CRIME_ID) from CRIME_OFFICERS)/(SELECT COUNT(OFFICER_ID) from OFFICERS))