工作中sql语句分析

–sql语句分析1
– Create table
create table YXC_TEST_PIGOUT_D
(
MONTH_PART VARCHAR2(255),
DAY_PART VARCHAR2(255),
CFFIELDID VARCHAR2(255),
FOUTQTY INTEGER,
FOUTWEIGHT VARCHAR2(255),
FDW28 VARCHAR2(255),
FPIGBATCHDAYS INTEGER,
FINPIGFARMID VARCHAR2(255),
FINSERVICEDEPTID VARCHAR2(255),
PIGTYPE VARCHAR2(255),
CFDR VARCHAR2(255),
CFBASESTATUS VARCHAR2(255),
FBIZDATE TIMESTAMP(6),
CFPIGVARIETIESID VARCHAR2(255),
FSEX VARCHAR2(255),
CFISSEEDPIG VARCHAR2(255),
CFCROSSLEVEL VARCHAR2(255)
);
–oracle中创建表的语句 常见的数据类型包含varchar2,timestamp,integer,number
– 添加表注释
comment on table YXC_TEST_PIGOUT_D is ‘肉猪调出’;
– 添加字段的注释
comment on column YXC_TEST_PIGOUT_D.MONTH_PART is ‘月分区’;
comment on column YXC_TEST_PIGOUT_D.DAY_PART is ‘日分区’;
comment on column YXC_TEST_PIGOUT_D.CFFIELDID is ‘分场编码’;
comment on column YXC_TEST_PIGOUT_D.FOUTQTY is ‘调出头数’;
comment on column YXC_TEST_PIGOUT_D.FOUTWEIGHT is ‘调出重量’;
comment on column YXC_TEST_PIGOUT_D.FDW28 is ‘还原28日龄重’;
comment on column YXC_TEST_PIGOUT_D.FPIGBATCHDAYS is ‘批次日龄’;
comment on column YXC_TEST_PIGOUT_D.FINPIGFARMID is ‘调入猪场’;
comment on column YXC_TEST_PIGOUT_D.FINSERVICEDEPTID is ‘调入服务部’;
comment on column YXC_TEST_PIGOUT_D.PIGTYPE is ‘猪只类型’;
comment on column YXC_TEST_PIGOUT_D.CFDR is ‘删除标识’;
comment on column YXC_TEST_PIGOUT_D.CFBASESTATUS is ‘状态编码’;
comment on column YXC_TEST_PIGOUT_D.FBIZDATE is ‘业务日期’;
comment on column YXC_TEST_PIGOUT_D.CFPIGVARIETIESID is ‘猪只品种’;
comment on column YXC_TEST_PIGOUT_D.FSEX is ‘猪只性别,1=公,2=母,公母混合=3’;
comment on column YXC_TEST_PIGOUT_D.CFISSEEDPIG is ‘种猪标识，1=是，2=否’;
comment on column YXC_TEST_PIGOUT_D.CFCROSSLEVEL is ‘杂交级别’;
–创建索引
create index INDEX_YXC_TEST_PIGOUT_D on YXC_TEST_PIGOUT_D (CFFIELDID);
create index INDEX_YXC_TEST_PIGOUT_D1 on YXC_TEST_PIGOUT_D (MONTH_PART);

–将一张表内的数据批量插入到另一张表(工作中一般都是全量字段的转移或者经过删选条件以后将特定的数据插入到目标表中)
insert into YXC_TEST_PIGOUT_D(month_part,day_part,cffieldid)
select
month_part,
day_part,
cffieldid
from DWR_A_FZ_PigOut_D

全量转移：
insert into YXC_TEST_PIGOUT_D
select
*
from DWR_A_FZ_PigOut_D

删除表中的数据,但是不删除表结构
truncate table YXC_TEST_PIGOUT_D
删除表中数据,同时删除表结构
drop table YXC_TEST_PIGOUT_D

–sql语句分析2
select
month_part,
indexid,
cnum
from(
select
t1.month_part as month_part,
–to_char((sum(t1.pzcnt)-sum(t1.cfcnt)-sum(t1.zerott))/nullif((sum(t1.batchcnt)-sum(t1.dccnt)),0)*100,‘FM99990.90’)||’%’ 完结批次利用率,
to_char(decode((sum(t1.batchcnt)-sum(t1.dccnt)),0,0,(sum(t1.pzcnt)-sum(t1.cfcnt)-sum(t1.zerott))/(sum(t1.batchcnt)-sum(t1.dccnt)))*100,‘FM99990.90’)||’%’ 完结批次利用率,
to_char(sum(t1.batchcnt),‘FM999999’) 完结批次总头数
from zbbi2017.DM_ENDBATCH_USERATE_D t1
left join ct_bas_fieldfile fc on fc.fid = t1.cffieldid
left join CT_BAS_MANAGEORG man on man.cfadminorgunitid=fc.cfstockorgid
WHERE substr(t1.month_part,0,4) = ‘2020’
and man.flongnumber like ‘01%’
group by t1.month_part
)
unpivot(cnum for indexid in (完结批次总头数 as ‘0101016’,完结批次利用率 as ‘0101017’))

在这段sql中需要学习的有下面几点：
常见的几个函数的使用：
sum() 求和函数
NULLIF(expr1,expr2)，如果expr1=expr2成立，那么返回值为null，否则返回值为expr1的值：而该语句中的nullif(a,0)是为了避免分子为零情况导致sql出错
同时还有另外一种用法就是decode()函数 如上面注释所示
to_char()函数
可以控制数字的数据格式
也可以将时间类型按照指定的形式输出
substr()截取字符串的函数

另外还有一个学习的就是行列转换的unpivot()
在转换前数据

转换后的数据：

其中指标0101016就是完结批次总头数，0101017就是完结批次利用率