通过sql识别和处理重复记录。

Create table OrderInfo
(
    ordernum    VARCHAR2(17),
    supplier    VARCHAR2(17),
    pol     VARCHAR2(17),
    pod     VARCHAR2(17),
    etd     DATE,
    eta         DATE,
    productcode VARCHAR2(17),
    qty     Number(10) 
)
/
Create table orderdetail 
(
    ordernum    VARCHAR2(17),
    product_code    VARCHAR2(17),
    productdesc VARCHAR2(50),
    barcode VARCHAR2(17),
    color       VARCHAR2(17),
    qty     number(10)  
)
/

Insert into orderinfo values ('OR12345','TATA','MUMBAI','CAIRO',To_Date('21/06/2013','dd/mm/yyyy'), To_Date('27/07/2013','dd/mm/yyyy'),'5025',10000 );

Insert into orderdetail values ('OR12345','5025','METALIC CLIPS','1234567890','RED',500);
Insert into orderdetail values ('OR12345','5025','METALIC CLIPS','1234567890','BLUE',500);
Insert into orderdetail values ('OR12345','5025','METALIC CLIPS','7890123456','GREEN',1000);
Insert into orderdetail values ('OR12345','5025','METALIC CLIPS','6789012345','YELLOW',1000);
Insert into orderdetail values ('OR12345','5025','METALIC CLIPS','5678901234','ORANGE',1000);
Insert into orderdetail values ('OR12345','5025','METALIC CLIPS','4567890123','PINK',1000);
Insert into orderdetail values ('OR12345','5025','METALIC CLIPS','3456789012','BROWN',1000);
Insert into orderdetail values ('OR12345','5025','METALIC CLIPS','2345678901','GREY',2500);
Insert into orderdetail values ('OR12345','5025','METALIC CLIPS','2345678901','SILVER',1500);

COMMIT
/


Output Required

Ordernum    supplier    productdesc barcode     color       qty etd     eta     pol pod
-------------------------------------------------------------------------------------------------------------------------------------
OR12345     TATA        METALIC CLIPS   1234567890  RED || BLUE 1000    21/06/2013  27/07/2013  MUMBAI  CAIRO
OR12345     TATA        METALIC CLIPS   7890123456  GREEN       1000    21/06/2013  27/07/2013  MUMBAI  CAIRO
OR12345     TATA        METALIC CLIPS   6789012345  YELLOW      1000    21/06/2013  27/07/2013  MUMBAI  CAIRO
OR12345     TATA        METALIC CLIPS   5678901234  ORANGE      1000    21/06/2013  27/07/2013  MUMBAI  CAIRO
OR12345     TATA        METALIC CLIPS   4567890123  PINK        1000    21/06/2013  27/07/2013  MUMBAI  CAIRO
OR12345     TATA        METALIC CLIPS   3456789012  BROWN       1000    21/06/2013  27/07/2013  MUMBAI  CAIRO
OR12345     TATA        METALIC CLIPS   2345678901  GREY || SILVER  4000    21/06/2013  27/07/2013  MUMBAI  CAIRO

+++++++++++++++++++++++++++++++

+ + + + + + + + + + + + + + + + + + + + + + + + + + + + + + +

If there is a duplicate barcode then concatenate the color and sum up the qty ….can this be achieved by sql query in oracle...any help would be appreciated

如果有重复的条码然后连接数量的颜色和总结....这可以通过oracle中的sql查询实现吗?如有任何帮助，我们将不胜感激。

2 个解决方案

#1

If you're using 11gR2 you can use listagg:

如果你使用11gR2，你可以使用listagg:

select oi.ORDERNUM, SUPPLIER, PRODUCTDESC,
sum(od.qty) qty, listagg(color, '||') within group (order by  oi.ORDERNUM) color,
BARCODE, ETA, POL, POD
from OrderInfo oi join orderdetail od on oi.ordernum = od.ordernum
group by oi.ORDERNUM, SUPPLIER, POL, POD, ETD, ETA, PRODUCTCODE , PRODUCTDESC, BARCODE

Here is a sqlfiddle demo

这是一个sqlfiddle演示。

Since you're not using 11gR2 you can either use one of the options in Alex Poole's link, or do it like this (with xmlagg):

由于您没有使用11gR2，您可以使用Alex Poole的链接中的一个选项，或者像这样(使用xmlagg):

select oi.ORDERNUM, SUPPLIER, PRODUCTDESC,
sum(od.qty) qty, 
rtrim(xmlagg(xmlelement(e,color || '||')).extract('//text()'), '||') color,
BARCODE, ETA, POL, POD
from OrderInfo oi join orderdetail od on oi.ordernum = od.ordernum
group by oi.ORDERNUM, SUPPLIER, POL, POD, ETD, ETA, PRODUCTCODE , PRODUCTDESC, BARCODE ;

Here is another sqlfiddle demo

这是另一个sqlfiddle演示。

#2

Try this

试试这个

SELECT oi.Ordernum
  ,supplier
  ,productdesc
  ,barcode
  ,listagg(color, ' || ') within group (order by color) as color
  ,SUM(od.qty) AS qty
  ,etd
  ,eta
  ,pol
  ,pod 
FROM orderinfo oi 
INNER JOIN orderdetail od ON oi.ordernum = od.ordernum
GROUP BY oi.Ordernum,supplier,productdesc,barcode,etd,eta,pol,pod

SQL FIDDLE DEMO

SQL小提琴演示

#1