用这条sql语句能够查出这些信息
A:select CODE_KEY3 as jig_code from pro_t_a05
where CODE_GRP = 'CONVEYCODE'
and CODE_KEY3 <> '*'
and CODE_KEY1 = 'C1'
and CODE_KEY2 = 'JI000'
jig_code
JIB1A
JIB1B
JIB2A
JIB3A
JIB3B
JIB4A
JIB4B
JIC1A
JIC1B用下面的sql语句可以查出这些
B: select
NVL(c.IRECT_LINE_MAN , 0.0) as line ,a.jig_code as jig_code,
a.DIV_CODE as div_code, NVL(b.NET_JPH , 0.0 ) as netjph ,
NVL(b.ACTTACKTIME, 0.0 ) as att
from PRO_T_I08 a ,(select * from PRO_T_Q03 where head='C1') b ,(select sum(DIRECT_LINE_MAN) as IRECT_LINE_MAN ,jig_code from PRO_T_I08 group by jig_code) c
where a.div_code='JI000'
and a.jig_code=b.jig_code(+)
and c.jig_code=a.jig_code
jig_code div_code
9 JIB1B JI000 0 5
9 JIB1B JI000 0 5
16 JIB1A JI000 0 6
现在要做的事,把这2条sql语句联合起来。B中的jig_code有数据的就展示,就是A中的jig_code都要展示出来。
应该是用外连接之类的,谁能帮忙看看,不太清楚的可以问
A:select CODE_KEY3 as jig_code from pro_t_a05
where CODE_GRP = 'CONVEYCODE'
and CODE_KEY3 <> '*'
and CODE_KEY1 = 'C1'
and CODE_KEY2 = 'JI000'
jig_code
JIB1A
JIB1B
JIB2A
JIB3A
JIB3B
JIB4A
JIB4B
JIC1A
JIC1B用下面的sql语句可以查出这些
B: select
NVL(c.IRECT_LINE_MAN , 0.0) as line ,a.jig_code as jig_code,
a.DIV_CODE as div_code, NVL(b.NET_JPH , 0.0 ) as netjph ,
NVL(b.ACTTACKTIME, 0.0 ) as att
from PRO_T_I08 a ,(select * from PRO_T_Q03 where head='C1') b ,(select sum(DIRECT_LINE_MAN) as IRECT_LINE_MAN ,jig_code from PRO_T_I08 group by jig_code) c
where a.div_code='JI000'
and a.jig_code=b.jig_code(+)
and c.jig_code=a.jig_code
jig_code div_code
9 JIB1B JI000 0 5
9 JIB1B JI000 0 5
16 JIB1A JI000 0 6
现在要做的事,把这2条sql语句联合起来。B中的jig_code有数据的就展示,就是A中的jig_code都要展示出来。
应该是用外连接之类的,谁能帮忙看看,不太清楚的可以问
SELECT *
FROM (SELECT CODE_KEY3 AS JIG_CODE
FROM PRO_T_A05
WHERE CODE_GRP = 'CONVEYCODE'
AND CODE_KEY3 <> '*'
AND CODE_KEY1 = 'C1'
AND CODE_KEY2 = 'JI000') X,
(SELECT NVL(C.IRECT_LINE_MAN, 0.0) AS LINE,
A.JIG_CODE AS JIG_CODE,
A.DIV_CODE AS DIV_CODE,
NVL(B.NET_JPH, 0.0) AS NETJPH,
NVL(B.ACTTACKTIME, 0.0) AS ATT
FROM PRO_T_I08 A,
(SELECT * FROM PRO_T_Q03 WHERE HEAD = 'C1') B,
(SELECT SUM(DIRECT_LINE_MAN) AS IRECT_LINE_MAN, JIG_CODE
FROM PRO_T_I08
GROUP BY JIG_CODE) C
WHERE A.DIV_CODE = 'JI000'
AND A.JIG_CODE = B.JIG_CODE(+)
AND C.JIG_CODE = A.JIG_CODE) Y
WHERE X.JIG_CODE = Y.JIG_CODE(+);
from (select CODE_KEY3 as jig_code
from pro_t_a05
where CODE_GRP = 'CONVEYCODE'
and CODE_KEY3 <> '*'
and CODE_KEY1 = 'C1'
and CODE_KEY2 = 'JI000') m
left join (select NVL(c.IRECT_LINE_MAN, 0.0) as line,
a.jig_code as jig_code,
a.DIV_CODE as div_code,
NVL(b.NET_JPH, 0.0) as netjph,
NVL(b.ACTTACKTIME, 0.0) as att
from PRO_T_I08 a,
(select * from PRO_T_Q03 where head = 'C1') b,
(select sum(DIRECT_LINE_MAN) as IRECT_LINE_MAN, jig_code
from PRO_T_I08
group by jig_code) c
where a.div_code = 'JI000'
and a.jig_code = b.jig_code(+)
and c.jig_code = a.jig_code) n on m.jig_code = n.jig_code
/--试一下:select NVL(c.IRECT_LINE_MAN, 0.0) as line,
a.jig_code as jig_code,
a.DIV_CODE as div_code,
NVL(b.NET_JPH, 0.0) as netjph,
NVL(b.ACTTACKTIME, 0.0) as att
from PRO_T_I08 a,
(select * from PRO_T_Q03 where head = 'C1') b,
(select jig_code, sum(DIRECT_LINE_MAN) as IRECT_LINE_MAN
from PRO_T_I08
group by jig_code) c,
(select CODE_KEY3 as jig_code
from pro_t_a05
where CODE_GRP = 'CONVEYCODE'
and CODE_KEY3 <> '*'
and CODE_KEY1 = 'C1'
and CODE_KEY2 = 'JI000') d
where a.div_code = 'JI000'
and a.jig_code = b.jig_code(+)
and c.jig_code = a.jig_code
and a.jig_code(+) = d.jig_code;
错误是这个
org.springframework.jdbc.UncategorizedSQLException: SqlMapClient operation; uncategorized SQLException for SQL []; SQL state [null]; error code [0];
--- The error occurred in gmdat/pro/IndexMng/ProductTotalStatus/dao/sqlmap/ConveyorOverspeed.xml.
--- The error occurred while applying a result map.
--- Check the listConveyorOverspeed-AutoResultMap.
--- The error happened while setting a property on the result object.
--- Cause: net.sf.cglib.beans.BulkBeanException; nested exception is com.ibatis.common.jdbc.exception.NestedSQLException:
--- The error occurred in gmdat/pro/IndexMng/ProductTotalStatus/dao/sqlmap/ConveyorOverspeed.xml.
--- The error occurred while applying a result map.
--- Check the listConveyorOverspeed-AutoResultMap.
--- The error happened while setting a property on the result object.
--- Cause: net.sf.cglib.beans.BulkBeanException
http://zhidao.baidu.com/question/64838405.html
from (select CODE_KEY3 as jig_code
from pro_t_a05
where CODE_GRP = 'CONVEYCODE'
and CODE_KEY3 <> '*'
and CODE_KEY1 = 'C1'
and CODE_KEY2 = 'JI000') m
left join (select NVL(c.IRECT_LINE_MAN, 0.0) as line,
a.jig_code as jig_code,
a.DIV_CODE as div_code,
NVL(b.NET_JPH, 0.0) as netjph,
NVL(b.ACTTACKTIME, 0.0) as att
from PRO_T_I08 a,
(select * from PRO_T_Q03 where head = 'C1') b,
(select sum(DIRECT_LINE_MAN) as IRECT_LINE_MAN, jig_code
from PRO_T_I08
group by jig_code) c
where a.div_code = 'JI000'
and a.jig_code = b.jig_code(+)
and c.jig_code = a.jig_code) n on m.jig_code = n.jig_code这条sql语句好用,但是不知道为什么往 INSERT INTO PRO_T_Q03
(head, div_code, jig_code, acttacktime, net_jph)
values
(#head#, #div_code#, #jig_code#, #att#, #netjph#)
插入数据后那条查询语句竟然查不出来,还必须在PRO_T_I08有相应的jig_code才能查询数据,,,怎么改啊
and c.jig_code=a.jig_code
还要匹配那个c 的和b的