oracle数据处理sql

--插入语句之不同表中相同字段的快捷插入
insert into z_nm_site_ent(site_ent_id,ent_name)  select site_ent_id,shop_name from z_nm_test

--这部分数据直接插入到主体和店铺中，但是需要关联上    且不在主体表中存在的
select *
  from Z_NM_SHOP_ALL a
where exists (select 1
          from (select count(1), shop_name
                  from Z_NM_SHOP_ALL
                 group by shop_name
                having(count(1) = 1))  b where a.shop_name=b.shop_name) and zc_name is null
               
-- 这部分数据是名称有重复的，所以主体只能插入一条  （查询name重复的数据条数）
select count(1),shop_name from  Z_NM_SHOP_ALL where   zc_name is null group by shop_name having(count(1)>1)
     --  需要把这些名称对应的店铺关联上
    
--- zc_name 不为空的
     select count(1),zc_name from  Z_NM_SHOP_ALL  where zc_name is not null   group by zc_name
      -- 同样把主体ID关联过来


--替换时间字段中的空字符
update z_nm_evment_all set EV_TIME=replace(EV_TIME,' ','')
where length(EV_TIME)>10 and EV_TIME like '% %' and length(EV_TIME)<12


--oracle判断
select case
when
length(EV_TIME)>10
then
to_date(EV_TIME,'yyyy-MM-dd hh24:mi:ss')




--拼接，没有日期的数据默认为某月1日
update  z_nm_evment_all set EV_TIME=EV_TIME || '/1'  where length(EV_TIME)=6


--查询指定字符串 在该字段中出现的次数
SELECT LENGTHB(TRANSLATE(EV_TIME,'/'|| EV_TIME,'/')) FROM z_nm_evment_all;

update z_nm_evment_all set  EV_TIME=EV_TIME || '/1' where LENGTHB(TRANSLATE(EV_TIME,'/'|| EV_TIME,'/'))!=2;


--评论插入sql

insert into z_nm_evaluation(EV_ID,SHOP_ID,EV_TIME,EV_USER,EV_CONTENT,IS_BAD,GOOD_ID)
select EV_ID,SHOP_ID,
case when
length(EV_TIME)>10
then
to_date(EV_TIME,'yyyy-MM-dd hh24:mi:ss')
else
to_date(EV_TIME,'yyyy-MM-dd')
end
,EV_USER,EV_CONTENT,IS_BAD,GOOD_ID from z_nm_evment_all


---两表关联 批量修改sql
update z_nm_site_ent a set a.ssgss=(select b.ssgss from z_nm_site_ent_info b where b.ent_name=a.ent_name and rownum =1)
where a.ent_name in (select b.ent_name from  z_nm_site_ent_info b)


修改同一张表中的重复数据， 把字段不为空的那一条数据字段 更新到字段为空的另一条数据字段中， z_nm_shop_id为重复数据的标识中间表（select shop_id from nm_shop group by shop_id having(count(1)>1) 例如重复数据的shop_id相同）

--修改店铺重复的数据字段，然后保留一条

update nm_shop w
   set w.ev_num =
       (select ev_num
          from nm_shop b
         where exists
         (select 1 from z_nm_shop_id h where h.shop_id = b.shop_id)
           and ev_num is not null
           and rownum = 1)
where w.ev_num is null
and exists (select 1 from z_nm_shop_id n where n.shop_id=w.shop_id)

---批量修改操作，并且只执行一条

update a_a_nm_shop_45 a  set a.type_code=
(select b.shop_sub_type from nm_shop_type b where a.shop_id=b.shop_id and rownum=1)





--删除重复
delete   from z_reg_bus_ent a
where rowid!=(select   max(rowid)   from z_reg_bus_ent b where a.ent_name=b.ent_name)

--查询时间段数据
select * from  nm_evaluation where to_char(to_date(ev_time),'yyyy-mm-dd')  like '%2017-04%'




--插入评论数据
insert into nm_evaluation
select ev_id, shop_id,to_date(ev_time,'yyyy-mm-dd'),ev_user,to_number(ev_num),ev_content,is_bad,add_time,ev_title,good_id from a_a_nm_evment_45



--统计评论量
select a.shop_id as 店铺编号,a.shop_name as 店铺名称,
a.platform_code as 平台编号,a.shop_address as 店铺地址 ,a.shop_url as url,
a.ev_num as 总评数量,a.bad_ev_num as 总差评数,
(select count(1) from nm_evaluation b where b.shop_id=a.shop_id and to_char(to_date(ev_time),'yyyy-mm-dd')  like '%2017-01%') as 啊1月总评论数,
(select count(1) from nm_evaluation b where b.shop_id=a.shop_id and b.is_bad='1' and to_char(to_date(ev_time),'yyyy-mm-dd')  like '%2017-01%') as 啊1月差评论数,
(select count(1) from nm_evaluation b where b.shop_id=a.shop_id and to_char(to_date(ev_time),'yyyy-mm-dd')  like '%2017-02%') as 啊2月总评论数,
(select count(1) from nm_evaluation b where b.shop_id=a.shop_id and b.is_bad='1' and to_char(to_date(ev_time),'yyyy-mm-dd')  like '%2017-02%') as 啊2月差评论数,
(select count(1) from nm_evaluation b where b.shop_id=a.shop_id and to_char(to_date(ev_time),'yyyy-mm-dd')  like '%2017-03%') as 啊3月总评论数,
(select count(1) from nm_evaluation b where b.shop_id=a.shop_id and b.is_bad='1' and to_char(to_date(ev_time),'yyyy-mm-dd')  like '%2017-03%') as 啊3月差评论数,
(select count(1) from nm_evaluation b where b.shop_id=a.shop_id and (to_char(to_date(ev_time),'yyyy-mm-dd')  like '%2017-04%' or to_char(to_date(ev_time),'yyyy-mm-dd')  like '%2017-05%' )) as 啊45月总评论数,
(select count(1) from nm_evaluation b where b.shop_id=a.shop_id and b.is_bad='1' and (to_char(to_date(ev_time),'yyyy-mm-dd')  like '%2017-04%' or to_char(to_date(ev_time),'yyyy-mm-dd')  like '%2017-05%' )) as 啊45月差评论数

from aa_nm_shop_1_5 a