欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

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

程序员文章站 2024-03-21 21:30:46
...
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;


 

相关标签: sql