oracle 查询
林杰龙
软件开发
展开
-
递归bom主料件的元件信息
set serveroutput on;exec dbms_output.enable(buffer_size => null);declare v_cnt type_file.num5%TYPE; v_flag type_file.chr1%TYPE; v_bma01 bma_file.bma01%TYPE; v_bmb03 bmb_file.bmb03%TYPE; t原创 2012-12-11 08:21:21 · 605 阅读 · 0 评论 -
不定行转列
SELECT liao1, SUBSTR (MAX (SYS_CONNECT_BY_PATH (liao2, ';')), 2) NAMEFROM (SELECT liao1, liao2, rn, LEAD (rn) OVER (PARTITION BY liao1 ORDER BY rn) rn1FROM (SELECT liao1, liao2, ROW_NUMBER () OVER (原创 2013-01-15 14:29:01 · 391 阅读 · 0 评论 -
更新bom元件代码(BOM双位查询)
update bmb_file set bmb03 = (select liao2 from xiaolin4 where bmb01 = liao1 and bmb03 = liao3) where (bmb01,bmb03) in (select liao1,liao3 from xiaolin4) and (bmb04 is not null and bmb04<=to_da原创 2013-01-14 16:57:59 · 481 阅读 · 0 评论 -
删除重复行并保留序列号较为小的一行
with tas (select 1 a ,10 b,11 c from dualunion allselect 2,10,11 from dualunion allselect 3,10,12 from dualunion allselect 4,10,13 from dualunion allselect 5,10,14 from dualunion allsel原创 2013-01-14 16:10:24 · 601 阅读 · 0 评论 -
使用简单函数
1.concat() concat(x,y)函数用于将y附加在x之后,该函数返回得到的字符串。例子:select concat(first_name,last_name)from customers;concat(first_name,last_name)----------------------------------------JohnbrownCynt原创 2013-01-16 08:03:44 · 367 阅读 · 0 评论 -
拆BOM
set serveroutput on;declare v_cnt type_file.num5%TYPE; v_flag type_file.chr1%TYPE; v_bma01 bma_file.bma01%TYPE; v_bmb03 bmb_file.bmb03%TYPE; type bma_cursor_type is ref cursor; ty原创 2012-12-19 15:50:49 · 506 阅读 · 0 评论 -
市价导入
select * from xiaolin2 for update delete xiaolin2drop table xiaolin2create table xiaolin2 ( liao2 varchar(100) not null, quy number(20,6) );select ima01,ima531 from ima_file where ima01 i原创 2012-12-19 15:34:18 · 296 阅读 · 0 评论 -
with 表名 as 用法
select * from xiaolin_erp_panfor updatedelete xiaolin_erp_pan select liao,cang,chu,pi,qty,(select ima01 from ima_file where imaud01 = liao and imaacti = 'Y') from xiaolin_erp_pandelete xiaolin原创 2012-12-19 15:32:57 · 1932 阅读 · 0 评论 -
去除空字符串并转大写
update ima_file set imaud01=replace(imaud01,chr(10),'') where imaud01 like '%' --此代码用空字符替代imaud01里的换行符(只作用于第一行为空白行的料件 0.1HZJM共276笔)update ima_file set imaud01=trim(imaud01)where ima01=ima01 --原创 2012-12-19 15:25:57 · 445 阅读 · 0 评论 -
修改数据表
--增加字段alter table xiaolin2 add (liao4 varchar2(20));--修改字段alter table xiaolin2 modify(liao4 varchar2(10));--删除字段alter table xiaolin2 drop(liao4);--删除表drop table xiaolin2;原创 2012-12-19 15:37:51 · 324 阅读 · 0 评论 -
创建类似另一张表并导入该表数据
create table lin_erp as select * from lee_erp_pan where 1=2 //where 1=1 数据导入select * from lin_erpdrop table xiaolincreate table lin_erp as select * from lee_erp_pan where 1=2select * from l原创 2012-12-19 15:31:11 · 389 阅读 · 0 评论 -
45 个非常有用的 Oracle 查询语句
日期/时间 相关查询获取当前月份的第一天运行这个命令能快速返回当前月份的第一天。你可以用任何的日期值替换 “SYSDATE”来指定查询的日期。12SELECT TRUNC (SYSDATE, 'MONTH') "First day of current month" FROM DUAL;转载 2015-01-23 09:00:06 · 848 阅读 · 0 评论