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编写和优化 博客分类: SQL sql
-
java.sql.SQLException: Parameter index out of range (1 > number of parameters, w 博客分类: java细节
-
sql 字符转ascii 博客分类: oracle-dba sql字符转ascii
-
oracle pl/sql实例练习 博客分类: Oracle SQLOracle编程数据结构F#
-
oracle pl/sql实例练习 博客分类: Oracle SQLOracle编程数据结构F#
-
用SQL语句复制记录 博客分类: SQL sql
-
从结果集中创建一个新的表,并将结果集的内容插入到新表中 博客分类: SQL sql复制表
-
从结果集中创建一个新的表,并将结果集的内容插入到新表中 博客分类: SQL sql复制表
-
用SQL语句复制记录 博客分类: SQL sql
-
SQL转 MongoDB语法速查表 博客分类: python&nodejs mongodb 语法