关于oracle sql group by 'not A group by expression‘

Question

Oracle10g，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

谢谢大家，现在我已经解决了problem.The查询是：

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

Answer 2

您应该添加不属于聚合函数的所有列。尝尝这个。

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 ,h.hbsag ,h.sgpt 
) t 

Answer 3

它的Oracle行为，除了聚合函数之外，所有选定的列都必须按以下方式分组。

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,h.hbsag,h.sgpt
        ) t

更新：

由于您只使用count，那么需要获取其他列吗？？试试这个..。

select count(*)
        from (
            select h.personal_info_id pid,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;