select a1.CSXH,a1.XH,a1.JCDWBM,a1.GY_XMSQBM,a1.MQJB,a1.QKBM,a1.CWMC,a1.JH,a1.ZBRBM,a1.CSBM,a1.CSMXBM,
a1.CSRQ,a1.YF,a1.CSQRCYE,a1.CSQRCYO,a1.CSQRHS,a2.RCYL,a2.RCOL,a2.HS,a2.BZfrom
( select CSXH,XH,JCDWBM,GY_XMSQBM,MQJB,QKBM,CWMC,JH,ZBRBM,CSBM,CSMXBM,
to_char(CSRQ,'YYYY-MM-DD') as CSRQ,YF,CSQRCYE,CSQRCYO,CSQRHS from TJ_CSYJRB01 )a1left join(Select SCRQ,JH,RCYL,RCOL,HS,BZ from DBA_RBO where
to_char(SCRQ,'YYYY-MM-DD')=(select to_char(RQ,'YYYY-MM-DD') from TJ_CSYJRB01_TIME))a2on a1.JH=a2.JHwhere to_char(CSRQ,'YYYY') as YEAR = to_char((select RQ from TJ_CSYJRB01_TIME),'YYYY')
order by a1.CSXH
a1.CSRQ,a1.YF,a1.CSQRCYE,a1.CSQRCYO,a1.CSQRHS,a2.RCYL,a2.RCOL,a2.HS,a2.BZfrom
( select CSXH,XH,JCDWBM,GY_XMSQBM,MQJB,QKBM,CWMC,JH,ZBRBM,CSBM,CSMXBM,
to_char(CSRQ,'YYYY-MM-DD') as CSRQ,YF,CSQRCYE,CSQRCYO,CSQRHS from TJ_CSYJRB01 )a1left join(Select SCRQ,JH,RCYL,RCOL,HS,BZ from DBA_RBO where
to_char(SCRQ,'YYYY-MM-DD')=(select to_char(RQ,'YYYY-MM-DD') from TJ_CSYJRB01_TIME))a2on a1.JH=a2.JHwhere to_char(CSRQ,'YYYY') as YEAR = to_char((select RQ from TJ_CSYJRB01_TIME),'YYYY')
order by a1.CSXH
解决方案 »
- Oracle10g linux下安装报Error in writing to directory /tmp/OraInstall....
- PLSQL Developer登陆的问题(oracle) 大神求帮忙啊~
- 数据库两个表连接问题
- 兄弟们快来帮忙啊,高分相赠
- pb+oracle 打包安装时,提示找不到wappc32.dll
- 带条件的联合查询,数据库是先联合两个表,还是先用条件过滤再联合两个表?
- 请问高手,Recordset 的问题!谢谢!
- 高分求助,取树型结构所有子结点
- 谁有 pl/sql developer 5.0 的注册号??(可用就加分)
- 请问Oracle8i和Oracle8i Personal Edition有什么区别?
- 求助!!第一次访问数据库的操作巨型慢
- merge into时索引失效问题
去掉 as year
a1.CSRQ,a1.YF,a1.CSQRCYE,a1.CSQRCYO,a1.CSQRHS,a2.RCYL,a2.RCOL,a2.HS,a2.BZfrom
( select CSXH,XH,JCDWBM,GY_XMSQBM,MQJB,QKBM,CWMC,JH,ZBRBM,CSBM,CSMXBM,
to_char(CSRQ,'YYYY-MM-DD') as CSRQ,YF,CSQRCYE,CSQRCYO,CSQRHS from TJ_CSYJRB01 )a1left join(Select SCRQ,JH,RCYL,RCOL,HS,BZ from DBA_RBO where
to_char(SCRQ,'YYYY-MM-DD')=(select to_char(RQ,'YYYY-MM-DD') from TJ_CSYJRB01_TIME))a2on a1.JH=a2.JH 提示SQL命令没有正确结束
select a1.*,a2.RCYL,a2.RCOL,a2.HS,a2.BZ
from
(select CSXH,XH,JCDWBM,GY_XMSQBM,MQJB,QKBM,CWMC,JH,ZBRBM,CSBM,CSMXBM,
to_char(CSRQ,'YYYY-MM-DD') as CSRQ,YF,CSQRCYE,CSQRCYO,CSQRHS from TJ_CSYJRB01 )a1
left join
(Select SCRQ,JH,RCYL,RCOL,HS,BZ from DBA_RBO where
exists(select 1 from TJ_CSYJRB01_TIME
where to_char(DBA_RBO.SCRQ,'YYYY-MM-DD')= to_char(RQ,'YYYY-MM-DD')
))a2
on a1.JH=a2.JH
1 with TJ_CSYJRB01 as (
2 select 1 CSXH,1 XH,1 JCDWBM,1 GY_XMSQBM,1 MQJB,1 QKBM,1 CWMC,1 JH,1 ZBRBM,1 CSBM,1 CSMXBM,SYSDA
3 1 YF,1 CSQRCYE, 1 CSQRCYO,1 CSQRHS FROM DUAL),
4 DBA_RBO AS (
5 SELECT SYSDATE SCRQ,1 JH,1 RCYL,1 RCOL,1 HS,1 BZ FROM DUAL),
6 TJ_CSYJRB01_TIME AS(
7 SELECT SYSDATE RQ FROM DUAL)
8 select a1.*,a2.RCYL,a2.RCOL,a2.HS,a2.BZ
9 from
10 (select CSXH,XH,JCDWBM,GY_XMSQBM,MQJB,QKBM,CWMC,JH,ZBRBM,CSBM,CSMXBM,
11 to_char(CSRQ,'YYYY-MM-DD') as CSRQ,YF,CSQRCYE,CSQRCYO,CSQRHS from TJ_CSYJRB01 )a1
12 left join
13 (Select SCRQ,JH,RCYL,RCOL,HS,BZ from DBA_RBO where
14 exists(select 1 from TJ_CSYJRB01_TIME
15 where to_char(DBA_RBO.SCRQ,'YYYY-MM-DD')= to_char(RQ,'YYYY-MM-DD')
16 ))a2
17* on a1.JH=a2.JH
QL> / CSXH XH JCDWBM GY_XMSQBM MQJB QKBM CWMC
--------- ---------- ---------- ---------- ---------- ---------- ----------
JH ZBRBM CSBM CSMXBM CSRQ YF CSQRCYE
--------- ---------- ---------- ---------- ---------- ---------- ----------
CSQRCYO CSQRHS RCYL RCOL HS BZ
--------- ---------- ---------- ---------- ---------- ----------
1 1 1 1 1 1 1
1 1 1 1 2010-11-06 1 1
1 1 1 1 1 1
with TJ_CSYJRB01 as (
select 1 CSXH,1 XH,1 JCDWBM,1 GY_XMSQBM,1 MQJB,1 QKBM,1 CWMC,1 JH,1 ZBRBM,1 CSBM,
1 CSMXBM,SYSDATE CSRQ,1 YF,1 CSQRCYE, 1 CSQRCYO,1 CSQRHS FROM DUAL),
DBA_RBO AS (
SELECT SYSDATE SCRQ,1 JH,1 RCYL,1 RCOL,1 HS,1 BZ FROM DUAL),
TJ_CSYJRB01_TIME AS(
SELECT SYSDATE RQ FROM DUAL)
select a1.*,a2.RCYL,a2.RCOL,a2.HS,a2.BZ
from
(select CSXH,XH,JCDWBM,GY_XMSQBM,MQJB,QKBM,CWMC,JH,ZBRBM,CSBM,CSMXBM,
to_char(CSRQ,'YYYY-MM-DD') as CSRQ,YF,CSQRCYE,CSQRCYO,CSQRHS from TJ_CSYJRB01 )a1
left join
(Select SCRQ,JH,RCYL,RCOL,HS,BZ from DBA_RBO where
exists(select 1 from TJ_CSYJRB01_TIME
where to_char(DBA_RBO.SCRQ,'YYYY-MM-DD')= to_char(RQ,'YYYY-MM-DD')
))a2
on a1.JH=a2.JH