写的第一个长sql,纪念下
来源:互联网 发布:电商部美工岗位说明书 编辑:程序博客网 时间:2024/04/27 14:46
select C_PD_CODE,
C_PD_NAME,
to_char(D_BEGIN, 'MM') AS D_MONTH,
D_BEGIN,
D_END,
D_END - D_BEGIN AS D_MARGIN,
D_SL_BEGIN,
D_SL_END,
C_DV_CUSTO_TYPE,
C_ORG_CODE,
N_PORT_COST,
N_SYL,
N_YIELD,
C_ZJYWSR,
C_YFSYLR
FROM (SELECT DISTINCT M.C_PD_CODE,
M.C_PD_NAME,
M.D_BEGIN,
M.D_END,
M.D_SL_BEGIN,
M.D_SL_END,
M.C_DV_CUSTO_TYPE,
M.C_ORG_CODE,
N.N_PORT_COST,
A.N_SYL,
B.N_YIELD,
C.C_ZJYWSR,
D.C_YFSYLR
FROM (SELECT PD.C_PD_CODE,
PD.C_PD_NAME,
PD.D_BEGIN,
PD.D_END,
PD.D_SL_BEGIN,
PD.D_SL_END,
PD.C_DV_CUSTO_TYPE,
PD.C_ORG_CODE
FROM T_L_PD_PRODUCT PD
WHERE pd.C_BRAND_CODE = 'LC_CF'
AND PD.n_Check_State = 1
AND PD.D_BEGIN between TO_DATE('20120101', 'yyyy-MM-dd') and
TO_DATE('20130605', 'yyyy-MM-dd')) M
LEFT JOIN (SELECT DISTINCT AST.N_PORT_COST,
AST.C_PORT_CODE,
AST.D_ASTSTAT,
AST.C_KM_CODE
FROM T_R_FR_ASTSTAT AST
WHERE AST.C_KEY_CODE = 'SSZB'
AND AST.C_KM_CODE = '811'
AND AST.D_ASTSTAT = TO_DATE('20130605', 'yyyy-MM-dd')
AND AST.N_PORT_COST != 0) N
ON N.C_PORT_CODE = M.C_PD_CODE
LEFT JOIN (select DISTINCT C_PD_CODE,
wmsys.wm_concat(c_pd_code_cls || '款' || ':' ||
N_YIELD * 100 || '%') AS N_SYL
from t_l_pd_yield
group by c_pd_code) A
ON A.C_PD_CODE = M.C_PD_CODE
LEFT JOIN (SELECT DISTINCT C_PD_CODE, N_YIELD FROM T_L_PD_YIELD) B
ON B.C_PD_CODE = M.C_PD_CODE
LEFT JOIN (SELECT DISTINCT C_PORT_CODE, C_KM_NAME AS C_ZJYWSR
FROM T_R_FR_ASTSTAT
WHERE C_KEY_CODE = 'ZZYWSR'
AND D_ASTSTAT = TO_DATE('20130605', 'yyyy-MM-dd')
AND C_KM_CODE = '923') C
ON C.C_PORT_CODE = M.C_PD_CODE
LEFT JOIN (SELECT DISTINCT C_PORT_CODE, C_KM_NAME AS C_YFSYLR
FROM T_R_FR_ASTSTAT
WHERE C_KEY_CODE = 'YFTZZLR'
AND D_ASTSTAT = TO_DATE('20130605', 'yyyy-MM-dd')
AND C_KM_CODE = '922') D
ON D.C_PORT_CODE = M.C_PD_CODE
ORDER BY D_BEGIN)
C_PD_NAME,
to_char(D_BEGIN, 'MM') AS D_MONTH,
D_BEGIN,
D_END,
D_END - D_BEGIN AS D_MARGIN,
D_SL_BEGIN,
D_SL_END,
C_DV_CUSTO_TYPE,
C_ORG_CODE,
N_PORT_COST,
N_SYL,
N_YIELD,
C_ZJYWSR,
C_YFSYLR
FROM (SELECT DISTINCT M.C_PD_CODE,
M.C_PD_NAME,
M.D_BEGIN,
M.D_END,
M.D_SL_BEGIN,
M.D_SL_END,
M.C_DV_CUSTO_TYPE,
M.C_ORG_CODE,
N.N_PORT_COST,
A.N_SYL,
B.N_YIELD,
C.C_ZJYWSR,
D.C_YFSYLR
FROM (SELECT PD.C_PD_CODE,
PD.C_PD_NAME,
PD.D_BEGIN,
PD.D_END,
PD.D_SL_BEGIN,
PD.D_SL_END,
PD.C_DV_CUSTO_TYPE,
PD.C_ORG_CODE
FROM T_L_PD_PRODUCT PD
WHERE pd.C_BRAND_CODE = 'LC_CF'
AND PD.n_Check_State = 1
AND PD.D_BEGIN between TO_DATE('20120101', 'yyyy-MM-dd') and
TO_DATE('20130605', 'yyyy-MM-dd')) M
LEFT JOIN (SELECT DISTINCT AST.N_PORT_COST,
AST.C_PORT_CODE,
AST.D_ASTSTAT,
AST.C_KM_CODE
FROM T_R_FR_ASTSTAT AST
WHERE AST.C_KEY_CODE = 'SSZB'
AND AST.C_KM_CODE = '811'
AND AST.D_ASTSTAT = TO_DATE('20130605', 'yyyy-MM-dd')
AND AST.N_PORT_COST != 0) N
ON N.C_PORT_CODE = M.C_PD_CODE
LEFT JOIN (select DISTINCT C_PD_CODE,
wmsys.wm_concat(c_pd_code_cls || '款' || ':' ||
N_YIELD * 100 || '%') AS N_SYL
from t_l_pd_yield
group by c_pd_code) A
ON A.C_PD_CODE = M.C_PD_CODE
LEFT JOIN (SELECT DISTINCT C_PD_CODE, N_YIELD FROM T_L_PD_YIELD) B
ON B.C_PD_CODE = M.C_PD_CODE
LEFT JOIN (SELECT DISTINCT C_PORT_CODE, C_KM_NAME AS C_ZJYWSR
FROM T_R_FR_ASTSTAT
WHERE C_KEY_CODE = 'ZZYWSR'
AND D_ASTSTAT = TO_DATE('20130605', 'yyyy-MM-dd')
AND C_KM_CODE = '923') C
ON C.C_PORT_CODE = M.C_PD_CODE
LEFT JOIN (SELECT DISTINCT C_PORT_CODE, C_KM_NAME AS C_YFSYLR
FROM T_R_FR_ASTSTAT
WHERE C_KEY_CODE = 'YFTZZLR'
AND D_ASTSTAT = TO_DATE('20130605', 'yyyy-MM-dd')
AND C_KM_CODE = '922') D
ON D.C_PORT_CODE = M.C_PD_CODE
ORDER BY D_BEGIN)
- 写的第一个长sql,纪念下
- 俺写的第一个javame程序,纪念一下
- 纪念项目中写的第一个存储过程
- 纪念自己用java写的第一个题目
- python3.x 纪念写的第一个爬虫
- Linux下的第一个Python程序,谨作纪念
- 第一个PL/SQL过程,纪念一下
- 看了一天ruby写的第一个脚本 ,仅仅用来纪念一下
- 纪念一下我的第一个操作系统
- 纪念自己的第一个软件
- 纪念自己的第一个应用上线
- 以此纪念我的第一个博客
- 纪念我追的第一个女孩
- 纪念下自己的第一个小站正式上线啦--*好趣拍*
- 本人写的第一个PL/SQL过程
- 【hdu 2544】 最短路 (dijkstra 写的第一个最短路,也是dijkstra算法,纪念一下)
- 第一次写大量的写js 留个纪念
- 第一个自己写的python程序:windows下备份文件
- 动态设置select默认值代码
- 在WCF使用SOAP1.1
- ViewPage判断左右滑动
- 图片的加载效果(水填充)
- 基于Predictive Parsing的ABNF语法分析器(六)——AbnfParser文法解析器之多个符号连接的情形(如rule和CRLF)
- 写的第一个长sql,纪念下
- 【Linux基础】为什么要学shell
- Android 组件----------SeekBar
- 最大似然估计
- Sybase datetime 时间转换格式 convert(varchar(10),字段名,转换格式)
- 《C Primer Plus》第六章编程题
- 字典树
- 【Linux基础】Bash Shell
- js实现目录树插件