Oracle

神甫

　　需求：如下面源数据，把子库、货位、物料的金额查出来。
　　可按照一个参数flag字段来判断是否要按照子库货位来分组；
　　如果flag = ‘N'，则不需要按照子库、货位，只需要按照物料来分组汇总金额；
　　如果flag = 'Y'，则按照子库、货位、物料分组汇总金额。
　　
　　
　　--源数据

SELECT 'A' subinv, 'a' locator, 10001 inventory_item_id, 100 amount
FROM dual
UNION
SELECT 'A' subinv, 'a' locator, 10001 inventory_item_id, 200 amount
FROM dual
UNION
SELECT 'A' subinv, 'c' locator, 10001 inventory_item_id, 500 FROM dual;

　　
　　--按照子库、货位、物料分组汇总金额

SELECT subinv, LOCATOR, inventory_item_id, SUM(amount)
FROM (SELECT 'A' subinv, 'a' locator, 10001 inventory_item_id, 100 amount
FROM dual
UNION
SELECT 'A' subinv, 'a' locator, 10001 inventory_item_id, 200 amount
FROM dual
UNION
SELECT 'A' subinv, 'c' locator, 10001 inventory_item_id, 500 FROM dual) t
GROUP BY subinv, LOCATOR, inventory_item_id;
　　
　　--根据参数判断是否需要根据子库、货位分组

SELECT decode('N', 'N', NULL, t.subinv), decode('N', 'N', NULL, LOCATOR), inventory_item_id, SUM(amount)
FROM (SELECT 'A' subinv, 'a' locator, 10001 inventory_item_id, 100 amount
FROM dual
UNION
SELECT 'A' subinv, 'a' locator, 10001 inventory_item_id, 200 amount
FROM dual
UNION
SELECT 'A' subinv, 'c' locator, 10001 inventory_item_id, 500 FROM dual) t
GROUP BY decode('N', 'N', NULL, t.subinv), decode('N', 'N', NULL, LOCATOR), inventory_item_id;
　　


　　--分组Flag作为参数传递进去

DECLARE
l_flag VARCHAR2(1) := 'Y'; --分组Flag
BEGIN
dbms_output.put_line('subinv,locator,inventory_item_id,amount_sum');
FOR rec IN (SELECT decode(l_flag, 'N', NULL, t.subinv) subinv
,decode(l_flag, 'N', NULL, t.locator) locator
,t.inventory_item_id
,SUM(t.amount) amount_sum
FROM (SELECT 'A' subinv
,'a' locator
,10001 inventory_item_id
,100 amount
FROM dual
UNION
SELECT 'A' subinv
,'a' locator
,10001 inventory_item_id
,200 amount
FROM dual
UNION
SELECT 'A' subinv
,'c' locator
,10001 inventory_item_id
,500
FROM dual) t
GROUP BY decode(l_flag, 'N', NULL, t.subinv)
,decode(l_flag, 'N', NULL, locator)
,inventory_item_id) LOOP
dbms_output.put_line(rec.subinv || ',' || rec.locator || ',' ||
rec.inventory_item_id || ',' || rec.amount_sum);
END LOOP;
END;