--[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
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
