关于oracle中的group by

Question

视图里面最好不要用group by

Answer 1

业务需要，而且这里直接把group  by拿掉会报不是单组分组函数的错误！！继续求解啊。。

Answer 2

应该是你的条件问题，你试试这样select userinfo.person_id,
       userinfo.user_name,
       count(devicebaseinfo.deviceID)
  from userinfo,
       (select *
          from devicebaseinfo
         where userinfo.deleteFlag <> -1
           and devicebaseinfo.deleteFlag <> -1) devicebaseinfo
 where userinfo.person_id = devicebaseinfo.inspectionPersonID(+)
 group by userinfo.user_name, userinfo.person_id;

Answer 3

select userinfo.person_id,
       userinfo.user_name,
       count(devicebaseinfo.deviceID)
  from userinfo, devicebaseinfo
 where userinfo.person_id = devicebaseinfo.inspectionPersonID(+)
   and userinfo.deleteFlag <> -1
   and devicebaseinfo.deleteFlag <> -1
 group by userinfo.user_name,userinfo.person_id;红色这段条件限制deleteFlag!=-1.
假如你只是要连接查询统计的话，可以修改为：and devicebaseinfo.deleteFlag(+) <> -1