实习生的orcale数据库学习记录

select a.userid,
       a.username,
       a.actual_cityid,
       b.city_name,
       c.county_id,
       c.county_name,
       a.grid_id,
       t.grid_name,
       a.createtime,
       a.enddate,
       a.status,
       d.deptid,
       d.title


  from mcd_core.user_user a


  left join mcd_core.DIM_PUB_CITY b
    on a.cityid = b.city_id
  left join mcd_core.DIM_PUB_COUNTY c
    on a.county = c.county_id
  left join mcd_core.DIM_PUB_GRID t
    on a.grid_id = t.grid_id
  left join mcd_core.USER_COMPANY d
    on a.departmentid = d.deptid


 where a.status = 0
   and c.county_id in (select county_id
                        from (select c.county_id, count(a.userid)
                              
                                from mcd_core.user_user a
                                left join mcd_core.DIM_PUB_COUNTY c
                                  on a.county = c.county_id
                               group by c.county_id
                               --order by count(a.userid) desc
                               having count(a.userid)> 5)
                       --where rownum = 1
                       );

 在公司使用orcale数据库做查询操作时候，一定要注意查询条件要具体，避免条件过于笼统，然后去使用 distinct  关键字去 降重，因为在公司所查询的而数据就单张主表而言都库往往是千级万级的，笼统的条件去查询，那查到的结果将是非常大的，这直接占用内存空间，非常吃资源，在运用左连接时候注意 ，伪表和主表的映射。