-- 查询 没有派单的 电能表
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 )