DCSIBB OC_NO SEQ_NO ITEM_NO QTY
1 1 A 10
1 2 B 20
2 1 C 30
3 1 A 60
4 1 B 50
SGMRIB PLAN_NO PLAN_SEQ_NO OC_NO SEQ_NO ITEM_NO REQ_QTY
1 1 1 1 A 10
1 2 1 2 B 20
2 1 2 1 C 30
3 1 3 1 A 20
4 1 3 1 B 30
SGMRAA WORK_NO ITEM_NO PLAN_QTY PLAN_NO PLAN_SEQ_NO
1 A 10 1 1
2 B 20 1 2
3 A 20 3 1
4 A 30 3 1
SGMRCA WORK_NO ITEM_NO IN_QTY
1 A 10
2 B 20
3 A 20
3 A 20 (做不足先入库)
结果: OC_NO SEQ_NO ITEM_NO QTY PLAN_QTY IN_QTY
1 1 A 10 10 10
1 2 B 20 20 20
2 1 C 30 30 30
3 1 A 60 50 40
4 1 B 50 NIL NIL
最好给出完整的表结构,测试数据,计算方法和正确结果.否则耽搁的是你宝贵的时间。
如果有多表,表之间如何关联?
发帖注意事项
http://topic.csdn.net/u/20091130/21/fb718680-98ff-4afb-98d8-cff2f8293ed5.html?24281
2. 计划
3. 工单
4. 入库单栏位已说明关系
结果是求的sql 来的
20 20
30 30
50 40
NIL NIL最后两列解释一下吧,看不明白...
SGMRIB.RIB009,SUM(RCA012),SUM(LOA003) FROM DCSIBB
LEFT JOIN SGMRIB ON (DCSIBB.OC_NO = SGMRIB.OC_NO) AND
(DCSIBB.OC_SEQ =SGMRIB.OC_SEQ) AND (DCSIBB.ITEM_NO = SGMRIB.ITEM_NO)
LEFT JOIN SGMRAA ON (SGMRAA.PLAN_NO = SGMRIB.PLAN_NO) AND (SGMRAA.PLAN_SEQ = SGMRIB.PLAN_SEQ
LEFT JOIN SGMRCA ON (SGMRCA.WORK_NO = SGMRAA.WORK_NO)
WHERE (RAA015 = IBB003 OR RAA015 IS NULL) AND IBB001 ='OC00456'
GROUP BY IBB001,IBB002,IBB003,IBB006,IBB013,RIB009,RAA001,RAA018,RAA019 ORDER BY IBB001 DESC, IBB002
SGMRIB.RIB009,SUM(RCA012),SUM(LOA003) FROM DCSIBB
LEFT JOIN SGMRIB ON (DCSIBB.OC_NO = SGMRIB.OC_NO) AND
(DCSIBB.OC_SEQ =SGMRIB.OC_SEQ) AND (DCSIBB.ITEM_NO = SGMRIB.ITEM_NO)
LEFT JOIN SGMRAA ON (SGMRAA.PLAN_NO = SGMRIB.PLAN_NO) AND (SGMRAA.PLAN_SEQ = SGMRIB.PLAN_SEQ
LEFT JOIN SGMRCA ON (SGMRCA.WORK_NO = SGMRAA.WORK_NO)
WHERE (RAA015 = IBB003 OR RAA015 IS NULL) AND IBB001 ='OC00456'
GROUP BY IBB001,IBB002,IBB003,IBB006,IBB013,RIB009,RAA001,RAA018,RAA019 ORDER BY IBB001 DESC, IBB002
怎么这么多group by ,很多没用的东西
你应该把sum放在SGMRAA,SGMRCAA中去做
LEFT JOIN (select sum(RCA012),work_no SGMRCA group by work_no) ON (M.WORK_NO = M.WORK_NO)
其它类似
LEFT JOIN (select sum(RCA012),work_no SGMRCA group by work_no) ON (M.WORK_NO = M.WORK_NO)
[Quote=引用 7 楼 abuying 的回复:]
SELECT a.OC_NO,a.SEQ_NO,a.ITEM_NO,
a.QTY--,SUM(RCA012),SUM(LOA003)
FROM DCSIBB a,SGMRIB b,SGMRAA c,SGMRCA d
WHERE (a.OC_NO = b.OC_NO)
AND (a.SEQ_NO =b.SEQ_NO)
AND (a.ITEM_NO = b.ITEM_NO)
AND (c.PLAN_NO = b.PLAN_NO)
AND (c.PLAN_SEQ_NO = b.PLAN_SEQ_NO)
AND (d.WORK_NO = c.WORK_NO)
-- AND (RAA015 = IBB003 OR RAA015 IS NULL)
-- AND IBB001 ='OC00456'
GROUP BY a.OC_NO,a.SEQ_NO,a.ITEM_NO,a.QTY
--IBB001,IBB002,IBB003,IBB006,IBB013,RIB009,RAA001,RAA018,RAA019
--ORDER BY IBB001 DESC, IBB002注释掉的那些东西哪来的?》
SGMRAA.ITEM_NO = DSCIBB.ITEM_NO OR SGMRAA.ITEM_NO IS NULL-- AND IBB001 ='OC00456'
DCSIBB.OC_NO = 'OC00456'
GROUP BY a.OC_NO,a.SEQ_NO,a.ITEM_NO,a.QTY
--IBB001,IBB002,IBB003,IBB006,IBB013,RIB009,RAA001,RAA018,RAA019
--ORDER BY IBB001 DESC, IBB002OC_NO, OC_SEQ,ITEM_NO,PLAN_QTY,WORK_NO....
ORDER BY OC_NO DESC, OC_SEQ
SGMRCA WORK_NO ITEM_NO IN_QTY
1 A 10
2 B 20
3 A 20
3 A 20 (做不足先入库)-------这行你确定没写错??
CREATE TABLE DCSIBB(OC_NO INT, SEQ_NO INT,ITEM_NO CHAR(1), QTY INT)
INSERT INTO DCSIBB
SELECT 1, 1, 'A', 10 UNION ALL
SELECT 1, 2, 'B', 20 UNION ALL
SELECT 2, 1, 'C', 30 UNION ALL
SELECT 3, 1, 'A', 60 UNION ALL
SELECT 4, 1, 'B', 50CREATE TABLE SGMRIB(PLAN_NO INT,PLAN_SEQ_NO INT,OC_NO INT,SEQ_NO INT,ITEM_NO CHAR(1),REQ_QTY INT)
INSERT INTO SGMRIB
SELECT 1, 1, 1, 1, 'A', 10 UNION ALL
SELECT 1, 2, 1, 2, 'B', 20 UNION ALL
SELECT 2, 1, 2, 1, 'C', 30 UNION ALL
SELECT 3, 1, 3, 1, 'A', 20 UNION ALL
SELECT 4, 1, 3, 1, 'B', 30
CREATE TABLE SGMRAA(WORK_NO INT, ITEM_NO CHAR(1),PLAN_QTY INT,PLAN_NO INT,PLAN_SEQ_NO INT)
INSERT INTO SGMRAA
SELECT 1, 'A', 10, 1, 1 UNION ALL
SELECT 2, 'B', 20, 1, 2 UNION ALL
SELECT 3, 'A', 20, 3, 1 UNION ALL
SELECT 4, 'A', 30, 3, 1 CREATE TABLE SGMRCA(WORK_NO INT,ITEM_NO CHAR(1),IN_QTY INT)
INSERT INTO SGMRCA
SELECT 1, 'A', 10 UNION ALL
SELECT 2, 'B', 20 UNION ALL
SELECT 3, 'A', 20 UNION ALL
SELECT 4, 'A', 20 SELECT a.OC_NO,a.SEQ_NO,a.ITEM_NO,
a.QTY,SUM(c.PLAN_QTY) PLAN_QTY,SUM(d.IN_QTY) IN_QTY
FROM DCSIBB a,SGMRIB b,SGMRAA c,SGMRCA d
WHERE (a.OC_NO = b.OC_NO)
AND (a.SEQ_NO =b.SEQ_NO)
AND (a.ITEM_NO = b.ITEM_NO)
AND (c.PLAN_NO = b.PLAN_NO)
AND (c.PLAN_SEQ_NO = b.PLAN_SEQ_NO)
AND (d.WORK_NO = c.WORK_NO)
AND (c.ITEM_NO = a.ITEM_NO OR c.ITEM_NO IS NULL)
--AND (a.OC_NO = 'OC00456')
GROUP BY a.OC_NO,a.SEQ_NO,a.ITEM_NO,a.QTY
--IBB001,IBB002,IBB003,IBB006,IBB013,RIB009,RAA001,RAA018,RAA019
--ORDER BY OC_NO DESC, SEQ_NO
--结果
OC_NO SEQ_NO ITEM_NO QTY PLAN_QTY IN_QTY
1 1 A 10 10 10
1 2 B 20 20 20
3 1 A 60 50 40先做到这 ,一会回来在看
你见我SUM 一个栏都 group 一大堆就知我SQL有几深道行.不如你帮我加上 就可以快快结帖. :-)
INSERT INTO DCSIBB
SELECT 1, 1, 'A', 10 UNION ALL
SELECT 1, 2, 'B', 20 UNION ALL
SELECT 2, 1, 'C', 30 UNION ALL
SELECT 3, 1, 'A', 60 UNION ALL
SELECT 4, 1, 'B', 50CREATE TABLE SGMRIB(PLAN_NO INT,PLAN_SEQ_NO INT,OC_NO INT,SEQ_NO INT,ITEM_NO CHAR(1),REQ_QTY INT)
INSERT INTO SGMRIB
SELECT 1, 1, 1, 1, 'A', 10 UNION ALL
SELECT 1, 2, 1, 2, 'B', 20 UNION ALL
SELECT 2, 1, 2, 1, 'C', 30 UNION ALL
SELECT 3, 1, 3, 1, 'A', 20 UNION ALL
SELECT 4, 1, 3, 1, 'B', 30
CREATE TABLE SGMRAA(WORK_NO INT, ITEM_NO CHAR(1),PLAN_QTY INT,PLAN_NO INT,PLAN_SEQ_NO INT)
INSERT INTO SGMRAA
SELECT 1, 'A', 10, 1, 1 UNION ALL
SELECT 2, 'B', 20, 1, 2 UNION ALL
SELECT 3, 'A', 20, 3, 1 UNION ALL
SELECT 4, 'A', 30, 3, 1 CREATE TABLE SGMRCA(WORK_NO INT,ITEM_NO CHAR(1),IN_QTY INT)
INSERT INTO SGMRCA
SELECT 1, 'A', 10 UNION ALL
SELECT 2, 'B', 20 UNION ALL
SELECT 3, 'A', 20 UNION ALL
SELECT 4, 'A', 20 SELECT a.OC_NO,a.SEQ_NO,a.ITEM_NO,
a.QTY,
SUM(c.PLAN_QTY) PLAN_QTY,
SUM(d.IN_QTY) IN_QTY
FROM DCSIBB a,SGMRIB b,SGMRAA c,SGMRCA d
WHERE (a.OC_NO = b.OC_NO)
AND (a.SEQ_NO =b.SEQ_NO)
AND (a.ITEM_NO = b.ITEM_NO)
AND (c.PLAN_NO = b.PLAN_NO)
AND (c.PLAN_SEQ_NO = b.PLAN_SEQ_NO)
AND (d.WORK_NO = c.WORK_NO)
AND (c.ITEM_NO = a.ITEM_NO OR c.ITEM_NO IS NULL)
--AND (a.OC_NO = 'OC00456')
GROUP BY a.OC_NO,a.SEQ_NO,a.ITEM_NO,a.QTY
--IBB001,IBB002,IBB003,IBB006,IBB013,RIB009,RAA001,RAA018,RAA019
--ORDER BY OC_NO DESC, SEQ_NOUNION ALL
SELECT x.OC_NO, x.SEQ_NO, x.ITEM_NO, x.QTY, NULL PLAN_QTY,NULL IN_QTY
FROM DCSIBB X
WHERE x.OC_NO=
(SELECT DISTINCT a.OC_NO FROM DCSIBB a,SGMRIB b
WHERE a.OC_NO NOT IN (SELECT OC_NO FROM SGMRIB))UNION ALL
SELECT y.OC_NO, y.SEQ_NO, y.ITEM_NO, y.QTY, y.QTY PLAN_QTY,y.QTY IN_QTY
FROM DCSIBB y
WHERE y.OC_NO=
(SELECT DISTINCT DCSIBB.OC_NO FROM DCSIBB,SGMRAA
WHERE DCSIBB.ITEM_NO NOT IN (SELECT ITEM_NO FROM SGMRAA))--结果
OC_NO SEQ_NO ITEM_NO QTY PLAN_QTY IN_QTY
1 1 A 10 10 10
1 2 B 20 20 20
3 1 A 60 50 40
4 1 B 50 NULL NULL
2 1 C 30 30 30NIL是字符不好UNION ALL改成NULL了
子查询返回的值多于一个。当子查询跟随在 =、!=、<、<=、>、>= 之后,或子查询用作表达式时,这种情况是不允许的。
和'WHERE y.OC_NO=' 的'='换成'IN'