Ticket #35: sql.txt

#ÇåÀíÉŸ³ý׎̬µÄÉÌÆ·£¬ÒÔŒ°Æä¹ØÁªµÄ±í£¬×Ü14žö±í
#Ô­ÀíÊÇÒÔÉÌÆ·ÊôÐÔ±í(ent_product_item)Ϊ±ê׌£¬ÏÈÉŸ³ýÆäÊýŸÝ£¬ÔÙ¹ØÁªÆäËû±íÇåÀí£š²»ŽæÔÚ¹ØÁª¹ØϵµÄÆäËû±íÊýŸÝ»á±»ÇåÀí£©
#²âÊÔÇåÀí14žö±íʱŒä>2Сʱ

#1¡¢ÉÌÆ·ÊôÐÔ±í(ent_product_item)
delete from ent_product_item where status=5 and last_update_date<sysdate-interval '30' day

#2¡¢ÉÌÆ·Ö÷±í(ent_product)
delete from ent_product t where not exists(select 1 from ent_product_item item where item.company_product_id=t.id)

#3¡¢ÉÌÆ·ŒÆÊýÆ÷(ent_company_product_counter)
delete from ent_company_product_counter t where not exists(select 1 from ent_product_item item where item.company_product_id=t.company_product_id)

#4¡¢ÉÌÆ·ä¯ÀÀŒÇÂŒ(ent_last_view_product)
delete from ent_last_view_product t where not exists(select 1 from ent_product_item item where item.company_product_id=t.product_id1)
delete from ent_last_view_product t where not exists(select 1 from ent_product_item item where item.company_product_id=t.product_id)

#5¡¢ÉÌÆ·ÏêÇé(ent_product_detail)
delete from ent_product_detail t where not exists(select 1 from ent_product_item item where item.company_product_id=t.company_product_id)

#6¡¢ÉÌÆ·ÄÚÈÝÉóºË(ent_product_content_audit)
delete from ent_product_content_audit t where not exists(select 1 from ent_product_item item where item.company_product_id=t.company_product_id)

#7¡¢ÉÌÆ·ÎïÁ÷·œÊœ(ent_product_logistics)
delete from ent_product_logistics t where not exists(select 1 from ent_product_item item where item.company_product_id=t.company_product_id)

#8¡¢ÉÌÆ·ÎïÁ÷·ÑÓÃ(ent_product_logistics_detail)
delete from ent_product_logistics_detail t where not exists(select 1 from ent_product_item item where item.company_product_id=t.product_logistics_id)

#9¡¢É̳ÇÁôÑÔ(ent_question)
delete from ent_question t where not exists(select 1 from ent_product_item item where item.company_product_id=t.target_id and t.type=1)

#10¡¢ÉÌŒÒÍÆŒöÉÌÆ·(ent_recommend_product)
delete from ent_recommend_product t where not exists(select 1 from ent_product_item item where item.company_product_id=t.company_product_id)

#11¡¢É̳ǟٱš(ent_report)
delete from ent_report t where not exists(select 1 from ent_product_item item where item.company_product_id=t.target_id and t.target_type=1)

#12¡¢×Ô¶šÒåÉÌÆ··ÖÀž(ent_shop_homepage_product)
delete from ent_shop_homepage_product t where not exists(select 1 from ent_product_item item where item.company_product_id=t.company_product_id)

#13¡¢×Ô¶šÒåÉÌÆ··ÖÀà(ent_self_product_type)
delete from ent_self_product_type t where not exists(select 1 from ent_product_item item where item.company_product_id=t.company_product_id)

#14¡¢œ»Ò×µã»÷ͳŒÆ(ent_trade_stat)
delete from ent_trade_stat t where not exists(select 1 from ent_product_item item where item.company_product_id=t.company_product_id)