本文共 4412 字，大约阅读时间需要 14 分钟。

--[color=red]插入语句之不同表中相同字段的快捷插入[/color] insert into z_nm_site_ent(site_ent_id,ent_name) select site_ent_id,shop_name from z_nm_test --[color=red]这部分数据直接插入到主体和店铺中，但是需要关联上 且不在主体表中存在的[/color] 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 -- [color=red]这部分数据是名称有重复的，所以主体只能插入一条 （查询name重复的数据条数）[/color] select count(1),shop_name from Z_NM_SHOP_ALL where zc_name is null group by shop_name having(count(1)>1) -- 需要把这些名称对应的店铺关联上 --- [color=red]zc_name 不为空的[/color] select count(1),zc_name from Z_NM_SHOP_ALL where zc_name is not null group by zc_name -- 同样把主体ID关联过来 --[color=red]替换时间字段中的空字符[/color] 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 --[color=red]oracle判断[/color] select case when length(EV_TIME)>10 then to_date(EV_TIME,'yyyy-MM-dd hh24:mi:ss') --[color=red]拼接，没有日期的数据默认为某月1日[/color] update z_nm_evment_all set EV_TIME=EV_TIME || '/1' where length(EV_TIME)=6 --[color=red]查询指定字符串 在该字段中出现的次数[/color] 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; --[color=red]评论插入sql[/color] 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 ---[color=red]两表关联 批量修改sql[/color] 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) [color=red] 修改同一张表中的重复数据， 把字段不为空的那一条数据字段 更新到字段为空的另一条数据字段中， z_nm_shop_id为重复数据的标识中间表（select shop_id from nm_shop group by shop_id having(count(1)>1) 例如重复数据的shop_id相同） [/color] --修改店铺重复的数据字段，然后保留一条 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) ---批量修改操作，并且只执行一条 [color=red]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)[/color] --[color=red]删除重复[/color] 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) --[color=red]查询时间段数据[/color] select * from nm_evaluation where to_char(to_date(ev_time),'yyyy-mm-dd') like '%2017-04%' --[color=red]插入评论数据[/color] 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 --[color=red]统计评论量[/color] 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

转载地址：https://blog.csdn.net/kai402458953/article/details/88340441 如侵犯您的版权，请留言回复原文章的地址，我们会给您删除此文章，给您带来不便请您谅解！