java - 关于oracle sql group by 'not a group by expression'

Question

这个问题已经有答案了:

ORA-00979 not a group by expression (10 个回答)

已关闭 5 年前。

Oracle 10g，sql抛出异常:“not a group by expression”

select count(*)
        from (
            select h.personal_info_id pid,h.hbsag hbsag,h.sgpt sgpt,MAX(h.date_reported)
            from health_checkup_info h
            inner join personal_info p on h.personal_info_id = p.id
            where
                h.deleted = 0
                and h.date_reported is not null
                and h.hbsag in(1,2)
                and p.deleted = 0

            group by h.personal_info_id
        ) t where t.hbsag=1 and t.sgpt>=20

然后，我更改了“group by”参数，添加“h.hbsag”“h.sgpt”，例如:

group by h.personal_info_id,h.hbsag,h.sgpt

但结果不正确。

Answer 1

谢谢大家，现在我已经解决了问题。查询是:

select count(*)
        from (
            select h.personal_info_id pid,h.hbsag,ROW_NUMBER() OVER (partition by h.personal_info_id order by h.date_reported desc) r
            from health_checkup_info h
            inner join personal_info p on h.personal_info_id = p.id
            where
                h.deleted = 0
                and h.date_reported is not null
                and h.hbsag in(1,2)
                and p.deleted = 0

        ) t where t.hbsag=2 and r=1