SQL编写和优化 博客分类： SQL sql

1、oracle层级关系SQL语句：
 	SELECT * FROM pdl_product_type START WITH id = ? CONNECT BY  id = PRIOR parent_id

2、sql优化
SELECT count(*) FROM pp_item i 
WHERE 1=1 
AND i.type_id=20807 
AND i.mall_id=1 
AND not exists (select md5_id from pp_store 
 where md5_id=i.md5_id and carriage=1 ); 

优化后：

SELECT count(*) FROM pp_item i 
WHERE 1=1 
AND i.type_id=20807 
AND i.mall_id=1 
AND not exists (select md5_id from pp_store 
 where md5_id=i.md5_id and carriage=1 GROUP BY md5_id ); 

3、
删数据
BEGIN;
DELETE a FROM pp_store a,pp_item b WHERE a.md5_id= b.md5_id AND b.title LIKE '%二手%' AND b.mall_id=11;
DELETE a FROM pp_item_match a,pp_item b WHERE a.md5_id= b.md5_id AND b.title LIKE '%二手%' AND b.mall_id=11;
DELETE FROM pp_item  WHERE title LIKE '%二手%'  AND mall_id=11;
COMMIT;

4、行列转换
方法一：
select * from (
select name,short_name,model_name,brandName,wm_concat(properties) as pros from (

select p.name,p.short_name,p.model_name,p.brand_name as brandName,t.key||':'||t.value as properties
from pdl_product_item t ,pdl_product_front p 
where t.product_id = p.id 
and p.smalltype_id = 20937 and p.price_show = 0 and t.value is not null 
)
group by name,short_name,model_name,brandName) where rownum <=100 ;

方法二：
create or replace function config_item_string(pid in Long)
return varchar2
is
      str_list varchar2(4000) default '';
      sp varchar2(20) default ':';
      tmp varchar2(4000) default null;
      str varchar2(20) default null;
begin
      for x in (select i.key as k,i.value as v from pdl_product_item i where i.product_id=pid and i.value is not null) loop
          tmp := to_char(x.k)|| sp ||to_char(x.v);
          str_list := str_list || str || tmp;
          str:='|';
          if lengthb(str_list)>3500 then
           str_list := str_list ||'$$';
          end if;
          exit when lengthb(str_list)>3500;
      end loop;
      return str_list;
end;

select
p.name,
p.short_name,
p.model_name,
p.brand_name,
config_item_string(p.id) as config_value
from pdl_product_front p 
where p.smalltype_id=20928 and p.hot_new<>3 and p.hot_new<>4 and rownum<=100;