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;