工作中常常用的查询语句

     
-- 查询 没有派单的 电能表
select * 
from Mn_Ammeter a, Mn_User u, Mn_Assign n, Mn_Meterinfo
where a.strammeterid = 
and a.  
        
  -- 派单任务
select * from MN_ASSIGN t


-- 用户  以及权限
select max(strUserId) from Mn_User u where strUserId like '4a45fc8b2cd990c1012cd9b647250%'
select *  from Mn_User u ,Mn_Role r where r.strrolename  like '%异常%'
select * from Mn_role r
select * from Mn_Permissionrole
select * from Mn_Permissionrole pr where pr.strnotes like  '%员工管理%'


-- 查看权限
select * from Mn_Permission perm where perm.strnotes like '%员工管理%' or perm.strpermissionname like '%员工管理%'


-- 查看角色 权限
select *  from mn_permissionrole perrole




--抄表记录 表
select distinct  m.strstatus  from Mn_Meterrecord m 


-- 核查 表
select distinct c.strstatus from Mn_Check c  where c.strstatus = '作废'


-- 派单表
select distinct a.strflag from Mn_Assign a




-- 查询用户 
select u.strpassword 密码 ,u.straccount 登陆名,u.strname 权限名 , u.straccount 用户  from Mn_User u  where u.strname = 'xiaoxiao'






-------------------------------------------------  新的数据结构


---- 建立采集 计划前的第一部分视图 ： 抄表段编号 抄表段名称 供电单位  电能表数目  客户数 表组id
create or replace view cl_collectplanv as
  select r_s.mr_sect_no, r_s.name  ,oor.org_name , count(distinct cmeter.meter_id) nometers ,count(cmp.mp_id) nousers--- ,dcbox.box_id ----,objp.geo_id ,obji.imageid,tbr.track_id
  from r_sect r_s, c_mp cmp,c_meter_mp_rela cmmr ,c_meter cmeter,o_org oor-----dc_box dcbox ,,obj_point objp ,obj_image obji ,track_box_rela tbr
  where 1= 1 
  and  r_s.mr_sect_no = cmp.mr_sect_no  -----计量点
  and  cmp.mp_id =cmmr.mp_id       ------计量点-电能表
  and cmeter.meter_id = cmmr.meter_id  ------电能表
 --- and dcbox.box_id(+)=  cmeter.box_id     ------表组  不管表组中会不会有电表对应 都会显示表组信息
  and r_s.org_no = oor.org_no
  ----and count(distinct cmeter.meter_id) >= count(cmp.mp_id) 
   group by  r_s.name ,r_s.mr_sect_no ,oor.org_name----dcbox.box_id ,,cmeter.meter_id,cmp.mp_id
   order by r_s.name 
     
------ 建立第二个视图 根据表组id 去寻找 图片，经纬度，轨迹，声音


select dc2b.box_id 表组id ,obj2i.imageid 图片id , cl2i.cl_collect_img_id 采集图片id ,
                           obj2p.geo_id 经纬度id ,cl2g.geo_collect_id 采集经纬度id,
                           t2i.track_id 轨迹点 ,  cl2t.cl_collect_track_id 采集轨迹点id 
                           
  from dc_box dc2b , obj_point obj2p ,obj_image obj2i ,track_box_rela t2br ,track_info t2i , cl_geopoint cl2g, cl_image cl2i ,cl_track cl2t 
  where 1=1
  and dc2b.box_id = obj2i.box_id
  and obj2i.imageid = cl2i.imageid --------------- 采集图片
  
  and obj2p.geo_id = dc2b.geo_id
  and obj2p.geo_id = cl2g.geo_id   --------------- 采集经纬度


  and dc2b.box_id = t2br.box_id
  and t2i.track_id = cl2t.track_id 
  and t2i.track_id = t2br.track_id  -------------- 采集轨迹 
  
  ------ 建立第二个视图 根据表组id 去寻找 图片，经纬度，轨迹，声音
create or replace view cl_collectplan2View as
select dc2b.box_id  ,obj2i.imageid , cl2i.cl_collect_img_id  ,
                           obj2p.geo_id ,cl2g.geo_collect_id ,
                           t2i.track_id , cl2t.cl_collect_track_id  
                           
  from dc_box dc2b , obj_point obj2p ,obj_image obj2i ,track_box_rela t2br ,track_info t2i , cl_geopoint cl2g, cl_image cl2i ,cl_track cl2t 
  where 1=1
  and dc2b.box_id = obj2i.box_id
  and obj2i.imageid = cl2i.imageid --------------- 采集图片
  
  and obj2p.geo_id = dc2b.geo_id
  and obj2p.geo_id = cl2g.geo_id   --------------- 采集经纬度


  and dc2b.box_id = t2br.box_id
  and t2i.track_id = cl2t.track_id 
  and t2i.track_id = t2br.track_id  -------------- 采集轨迹 
  
-------------------- 实现三个
  




-----------------员工表
select o.name ,o.staff_no from o_staff o, o_dept tept where o.dept_no = tept.dept_no and tept.name like '抄收%'


select rseam.mr_sect_no    抄表段编号,
       rseam.name          抄表段名,
       cmam.meter_id      电表标志,
       ccam.elec_addr      用电地址,
       ccam.elec_type_code 用电类别,
       ccam.cons_name      用户名,
       ccam.cons_no        用户编号,
       gam.tg_name         所属台区,
       gam.pub_priv_flag   供电性质,
       cmam.box_id         表组id
       ,cmam.inst_loc      安装位置
  from r_Sect rseam, C_mp cmpam, c_Cons ccam, G_tg gam ,c_meter_mp_rela cmmam ,c_meter cmam ,d_meter dmeam
 where 1 = 1
   and rseam.mr_sect_no = cmpam.mr_sect_no
   and ccam.cons_id = cmpam.cons_id
   and cmpam.tg_id = gam.tg_id
   and cmpam.mp_id = cmmam.mp_id
   and cmam.meter_id = cmmam.meter_id
   and cmam.meter_id = dmeam.meter_id
   and rseam.mr_sect_no like '%16070023%'
    
    select
        rseam.mr_sect_no ,
        rseam.name ,
        dmeam.asset_no,
        ccam.elec_addr ,
        ccam.elec_type_code ,
        ccam.cons_name ,
        ccam.cons_no ,
        gam.tg_name  ,
        gam.pub_priv_flag ,
        cmam.box_id ,
        cmam.inst_loc  
    from
        r_Sect rseam,
        C_mp cmpam,
        c_Cons ccam,
        G_tg gam ,
        c_meter_mp_rela cmmam ,
        c_meter cmam ,
        d_meter dmeam 
    where
        1 = 1 
        and rseam.mr_sect_no = cmpam.mr_sect_no 
        and ccam.cons_id = cmpam.cons_id  
        and cmpam.tg_id = gam.tg_id 
        and cmpam.mp_id = cmmam.mp_id 
        and cmam.meter_id = cmmam.meter_idand cmam.meter_id = dmeam.meter_id 
        and rseam.mr_sect_no like  '%1607002341%'   
   
---采集派单   renderer:showValue,
select * from Cl_Assign
   
-----采集计划  
select clppl.collect_plan_id , clppl.plan_type , clppl.plan_man_id ,clppl.plan_date ,clppl.collect_type, ospl.name ,clppl.start_date ,clppl.end_date ,clppl.notes
  from cl_plan clppl, o_staff ospl
 where ospl.staff_no = clppl.collect_man_id  order by clppl.collect_plan_id 
   
-----采集计划数目
select count (*)  from cl_plan clppl, o_staff ospl
 where ospl.staff_no = clppl.collect_man_id    order by clppl.collect_plan_id 
   
   
----根据 采集计划id 找去 计划id 对应的 采集对象id


select clbam.collect_object_id ,clbam.collect_plan_id
   from cl_object clbam where clbam.collect_plan_id = '116'  order by collect_object_id ;
   
   
select * from cl_plan  order by collect_plan_id


select * from cl_object


select  claa.cl_assign_id ,claa.collect_plan_id ,claa.collect_object_id ,
        claa.cl_reassign_apply_id,claa.current_collect_man_id,
        claa.cl_assign_man_id ,claa.assign_date ,claa.cl_start_date,
        claa.cl_end_date ,claa.cl_assign_status ,claa.notes
       from cl_assign claa


select * from cl_assign




DELETE  FROM cl_object


DELETE FROM cl_plan


delete from cl_assign




select * from o_Permission for  upda


--------------------------采集计划查询
select clawu.cl_assign_man_id 派单人,
       clawu.current_collect_man_id 现采集人,
       cl_object.meter_id      电能表标志,
       cl_object.mr_sect_no    抄表段id,
       clawu.assign_date      派单日期,
       clawu.cl_start_date    派单开始日期,
       clawu.cl_end_date      派单结束日期,
       clawu.cl_assign_status 派单状态,
       objpwu.lng             经度,
       objpwu.lat             纬度,
       objpwu.alt             高度,
       objpwu.error_range     偏差,
       objiwu.imageid         图片id,
       track_info.track_id    轨迹id,
       obj_sound.sound_id     声音id
  from cl_assign   clawu,cl_object,
       cl_geopoint clgwu,
       obj_point   objpwu ,
       obj_image objiwu,
       cl_image,
       cl_track,
       track_info,
       cl_sound,
       obj_sound
 where 
      clawu.collect_object_id =  cl_object.collect_object_id
   and clawu.cl_assign_id = clgwu.cl_assign_id(+)
   and clgwu.geo_id = objpwu.geo_id(+)
   
   and clawu.cl_assign_id = cl_image.cl_assign_id(+)
   and cl_image.imageid =  objiwu.imageid (+)
   
   and clawu.cl_assign_id = cl_track.cl_assign_id(+)
   and cl_track.track_id = track_info.track_id(+)
   
   and clawu.cl_assign_id = cl_sound.cl_assign_id(+)
   and cl_sound.sound_id = obj_sound.sound_id(+)
 --  order by cl_object.collect_object_id
--  group by cl_object.mr_sect_no


select count(*)
  from cl_assign   clawu,cl_object,
       cl_geopoint clgwu,
       obj_point   objpwu ,
       obj_image objiwu,
       cl_image,
       cl_track,
       track_info,
       cl_sound,
       obj_sound
 where 
      clawu.collect_object_id =  cl_object.collect_object_id
   and clawu.cl_assign_id = clgwu.cl_assign_id(+)
   and clgwu.geo_id = objpwu.geo_id(+)
   
   and clawu.cl_assign_id = cl_image.cl_assign_id(+)
   and cl_image.imageid =  objiwu.imageid (+)
   
   and clawu.cl_assign_id = cl_track.cl_assign_id(+)
   and cl_track.track_id = track_info.track_id(+)
   
   and clawu.cl_assign_id = cl_sound.cl_assign_id(+)
   and cl_sound.sound_id = obj_sound.sound_id(+)








--------------------------采集派工查询


select count(*)
  from r_sect r
 where EFFECT_FLAG = 1
   and rownum < 20


select  mr_sect_no, org_no,name,attr, effect_flag, status_date,CONS_COUNT,METER_COUNT,geo_COUNT,TRACK_COUNT from (
select mr_sect_no, org_no,name,attr, effect_flag, status_date,
  (select count(C_MP.CONS_ID) from C_MP where C_MP.MR_SECT_NO = r.mr_sect_no) CONS_COUNT, 
  (select count(cr.meter_id) from C_MP, C_METER_MP_RELA cr  where C_MP.MP_ID = cr.mp_id and C_MP.MR_SECT_NO = r.mr_sect_no) METER_COUNT, 
  (select count(geo_id) from dc_box  where dc_box.mr_sect_no = r.mr_sect_no) geo_COUNT, 
           (select count(br.box_id) from TRACK_BOX_RELA br, dc_box dbox  where br.box_id = dbox.box_id  and dbox.mr_sect_no = r.mr_sect_no) TRACK_COUNT 
            from r_sect r 
  where EFFECT_FLAG = 1 and rownum < 20 )




select *  from c_mp where rownum between 5 and 15 order by c_mp.mp_id DESC 






SELECT * FROM 
(
SELECT  A.*, ROWNUM RN 
FROM (SELECT * FROM  c_mp  order by c_mp.mp_id DESC ) A--- where ROWNUM  between 5 and 15
)
WHERE RN BETWEEN 21 AND 40


select * from (
select cmmmc.* , rownum aaaa  from
(select * from c_mp )  cmmmc where  rownum < 10--- between 10 and 14
)
---where aaaa >9--- between 10 and 14






----学习 数据库


-- 左右连接
select * from emp ,dept where emp.deptno (+)= dept.deptno and dept.deptno = 40


-- 1999 笛卡尔集  自然连接
select * from emp ,dept
select * from emp natural join dept
---指定消除笛卡尔集
select * from emp join dept using (deptno)




--- 分组查询之后，只能出现改字段和统计函数， nal( , 0) ,函数是用来设定default的
select  dept.dname ,count(dept.deptno) ,nvl(sum(emp.sal),0)
  from emp, dept
 where emp.deptno (+)= dept.deptno --and avg(emp.sal) >2000
  group by dept.dname
  having avg(emp.sal) >2000


--- 测试
select (select dept.deptno from dept) dno ,* from emp where dno = emp.deptno where job <> 'SALESMAN' group by job order by sums ASC
select * from emp order by sal


--- /验证 语句
--- 验证子查询 在where 中主要返回 单行单列 ，多行单列，单行多列
select * from emp where sal > (select avg (sal)  from emp )
--- 验证子查询 在where 中主要返回 单行多列
select * from emp where (job ,sal) =(select job ,sal from emp where emp.ename = 'SMITH')
--- 验证 子查询总 在where 中返回 多行单列
--- ；有关 in 的操作
---注意如果 not in  中有null 那么不会有任何结果返回
select * from emp where sal in (select sal from emp where emp.ename <> 'SMITH')


-- 有关 any  操作  = 任何一个  > 比最小的要大，  < 比最大的要小
select * from emp where sal >any (select sal from emp where emp.sal > 2000)


-- 有关 all  操作  = (如果是多行，则不会有结果)  > 比最大的要大，  < 比最小的要小
select * from emp where sal =all (select sal from emp where  emp.sal > 2000 )