select a.A_id,a.stock,a.Item_id, sum(c.number) NumberCount from 表A a, 表B b,表B1 c where a.A_id = b.A_id and a.stock = b.stock and b.b_id = c.b_id group by a.A_id,a.stock,a.Item_id
select a.A_id,a.stock,a.Item_id, sum(c.number) NumberCount from 表A a, 表B b,表B1 c where a.A_id = b.A_id and a.stock = b.stock and b.b_id = c.b_id and a.A_id=20 group by a.A_id,a.stock,a.Item_id
select a.A_id,a.stock,a.Item_id, sum(c.number) NumberCount from 表A a, 表B b,表B1 c where a.A_id = b.A_id and a.stock = b.stock and a.itemid=b1.itemid and b.b_id = c.b_id and a.A_id=20 group by a.A_id,a.stock,a.Item_id
declare v_a_id number:=20; begin select a_id,a.stock,a.item_id,c.numbercout from a,(select b.a_id,b.stock,b1.iten_id,numbercout=sum(Number) from b join b1 on b.b_id=b1.b_id where b.a_id=v_a_id group by b.a_id,b.stock,b1.iten_id) c where a.a_id=c.a_id and a.stock=c.stock end
select a.A_id,a.stock,a.Item_id,c.num from a,b,(select B_id,Item_id,sum(number) num from b1 group by B_id,Item_id) c where a.id=b.A_id and a.stock=b.stock and b.B_id=c.B_id and a.id=20 and a.Item_id=c.Item_idresult: 20 CC 5 260 20 CC 5 260 20 CC 6 340 20 DD 9 90
SELECT A.A_ID,A.STOCK,A.ITEM_ID,DECODE(C.NUMBERCOUNT,NULL,0,C.NUMBERCOUNT) NUMBERCOUNT FROM A, ( SELECT B1.B_ID,B1.ITEM_ID,B.A_ID,B.STOCK,SUM(NUMBERCOUNT) NUMBERCOUNT FROM B1,B WHERE B.B_ID=B1.B_ID GROUP BY B1.B_ID,B1.ITEM_ID,B.A_ID,B.STOCK ) C WHERE A.A_ID=C.A_ID(+) AND A.STOCK=C.STOCK(+) AND A.ITEM_ID=C.ITEM_ID(+) AND A.A_ID=20 显示结果: A_ID STOCK ITEM_ID NUMBERCOUNT 20 CC 5 260 20 CC 5 260 20 CC 6 340 20 CC 8 0 20 DD 9 90 随便说一下,在ORACLE中表的字段名称不能用number,我把这个字段改成NUMBERCOUNT 现在得到的结果只是和你的顺序不一样,如果非得要一样的话,加一个"ORDER BY A.ROWID"就可以了 如下: SELECT A.A_ID,A.STOCK,A.ITEM_ID,DECODE(C.NUMBERCOUNT,NULL,0,C.NUMBERCOUNT) NUMBERCOUNT FROM A, ( SELECT B1.B_ID,B1.ITEM_ID,B.A_ID,B.STOCK,SUM(NUMBERCOUNT) NUMBERCOUNT FROM B1,B WHERE B.B_ID=B1.B_ID GROUP BY B1.B_ID,B1.ITEM_ID,B.A_ID,B.STOCK ) C WHERE A.A_ID=C.A_ID(+) AND A.STOCK=C.STOCK(+) AND A.ITEM_ID=C.ITEM_ID(+) AND A.A_ID=20 ORDER BY A.ROWID 显示结果: A_ID STOCK ITEM_ID NUMBERCOUNT 20 CC 5 260 20 CC 6 340 20 CC 8 0 20 DD 9 90 20 CC 5 260 现在和你的要求一样了吧! 给分吧!!!! 呵呵!
后来我们的解决方法如下: [数据正确,在几个表都有20万条记录的前提下,运算费时: 0.75秒 请大伙帮忙指点有哪些缺陷,多谢指教!]CREATE OR REPLACE PROCEDURE pro_ycsoe_seek1 ( v_table_id IN NUMBER, --(10) v_cur OUT pag_stock_1.cur_stock1 ) AS BEGIN OPEN v_cur FOR SELECT a.f_rcd_id , a.f_table_id, a.f_lines, a.f_stock_id, c.f_name f_stock_name, a.f_product_id, b.f_product_bm, b.f_product_type, b.f_product_name, b.f_product_area, b.f_unit, a.f_number_unit, NVL (d.f_sale_order_number, 0) f_sale_order_number, NVL (d.f_sale_number, 0) f_sale_number, a.f_rebate, a.f_price_unit_o, a.f_price_unit_c, a.f_be_to_sale_order, a.f_brief FROM yc_sale_order_entry a LEFT OUTER JOIN (select b.f_stock_id, b1.f_product_id, SUM (b1.f_number_sell) f_sale_number, SUM (b1.f_number_unit) f_sale_order_number from sale_order_entry b1 join sale_order b using (f_table_id) where b.f_yc_order_table_id = v_table_id and b.f_be_cancel<>1 GROUP BY f_stock_id, f_product_id ) d ON a.f_product_id = d.f_product_id AND a.f_stock_id = d.f_stock_id LEFT OUTER JOIN stock c ON a.f_stock_id = c.f_stock_id INNER JOIN product b ON a.f_product_id = b.f_product_id WHERE a.f_table_id = v_table_id ORDER BY a.f_lines; END; /
from 表A a, 表B b,表B1 c
where a.A_id = b.A_id and a.stock = b.stock
and b.b_id = c.b_id
group by a.A_id,a.stock,a.Item_id
from 表A a, 表B b,表B1 c
where a.A_id = b.A_id and a.stock = b.stock
and b.b_id = c.b_id and a.A_id=20
group by a.A_id,a.stock,a.Item_id
from 表A a, 表B b,表B1 c
where a.A_id = b.A_id and a.stock = b.stock and a.itemid=b1.itemid
and b.b_id = c.b_id and a.A_id=20
group by a.A_id,a.stock,a.Item_id
v_a_id number:=20;
begin
select a_id,a.stock,a.item_id,c.numbercout
from a,(select b.a_id,b.stock,b1.iten_id,numbercout=sum(Number)
from b join b1 on b.b_id=b1.b_id where b.a_id=v_a_id
group by b.a_id,b.stock,b1.iten_id) c
where a.a_id=c.a_id and a.stock=c.stock
end
from a,b,(select B_id,Item_id,sum(number) num from b1 group by B_id,Item_id) c
where a.id=b.A_id and a.stock=b.stock and b.B_id=c.B_id
and a.id=20 and a.Item_id=c.Item_idresult:
20 CC 5 260
20 CC 5 260
20 CC 6 340
20 DD 9 90
FROM A,
(
SELECT B1.B_ID,B1.ITEM_ID,B.A_ID,B.STOCK,SUM(NUMBERCOUNT) NUMBERCOUNT
FROM B1,B
WHERE B.B_ID=B1.B_ID
GROUP BY B1.B_ID,B1.ITEM_ID,B.A_ID,B.STOCK
) C
WHERE A.A_ID=C.A_ID(+) AND
A.STOCK=C.STOCK(+) AND
A.ITEM_ID=C.ITEM_ID(+) AND
A.A_ID=20
显示结果:
A_ID STOCK ITEM_ID NUMBERCOUNT
20 CC 5 260
20 CC 5 260
20 CC 6 340
20 CC 8 0
20 DD 9 90
随便说一下,在ORACLE中表的字段名称不能用number,我把这个字段改成NUMBERCOUNT
现在得到的结果只是和你的顺序不一样,如果非得要一样的话,加一个"ORDER BY A.ROWID"就可以了
如下:
SELECT A.A_ID,A.STOCK,A.ITEM_ID,DECODE(C.NUMBERCOUNT,NULL,0,C.NUMBERCOUNT) NUMBERCOUNT
FROM A,
(
SELECT B1.B_ID,B1.ITEM_ID,B.A_ID,B.STOCK,SUM(NUMBERCOUNT) NUMBERCOUNT
FROM B1,B
WHERE B.B_ID=B1.B_ID
GROUP BY B1.B_ID,B1.ITEM_ID,B.A_ID,B.STOCK
) C
WHERE A.A_ID=C.A_ID(+) AND
A.STOCK=C.STOCK(+) AND
A.ITEM_ID=C.ITEM_ID(+) AND
A.A_ID=20
ORDER BY A.ROWID
显示结果:
A_ID STOCK ITEM_ID NUMBERCOUNT
20 CC 5 260
20 CC 6 340
20 CC 8 0
20 DD 9 90
20 CC 5 260
现在和你的要求一样了吧!
给分吧!!!!
呵呵!
v_table_id IN NUMBER,
--(10)
v_cur OUT pag_stock_1.cur_stock1
)
AS
BEGIN
OPEN v_cur FOR
SELECT a.f_rcd_id
, a.f_table_id, a.f_lines, a.f_stock_id,
c.f_name f_stock_name, a.f_product_id, b.f_product_bm,
b.f_product_type, b.f_product_name, b.f_product_area, b.f_unit,
a.f_number_unit,
NVL (d.f_sale_order_number, 0) f_sale_order_number,
NVL (d.f_sale_number, 0) f_sale_number, a.f_rebate,
a.f_price_unit_o, a.f_price_unit_c, a.f_be_to_sale_order,
a.f_brief
FROM yc_sale_order_entry a LEFT OUTER JOIN
(select b.f_stock_id, b1.f_product_id,
SUM (b1.f_number_sell) f_sale_number,
SUM (b1.f_number_unit) f_sale_order_number
from sale_order_entry b1 join sale_order b using (f_table_id)
where b.f_yc_order_table_id = v_table_id and
b.f_be_cancel<>1
GROUP BY f_stock_id, f_product_id
) d
ON a.f_product_id = d.f_product_id
AND a.f_stock_id = d.f_stock_id
LEFT OUTER JOIN stock c ON a.f_stock_id = c.f_stock_id
INNER JOIN product b ON a.f_product_id = b.f_product_id
WHERE a.f_table_id = v_table_id
ORDER BY a.f_lines;
END;
/