sql - 避免在一组记录中重复值

我确定之前已经回答过这个问题，但问题是我不知道如何寻找解决方案。

我有一个 Oracle 查询，它给了我这个结果:

ETA     Vessel   Line   POD   SZTP QTY
====    ======   ====   ===   ==== ===
26/12   MAEWIS   MAE    LIV   40RH  23
26/12   MAEWIS   MAE    PBL   40RH  12
26/12   APLMEN   APL    PTR   20DR  44
26/12   APLMEN   APL    TRU   20DR  22
27/12   APLMEN   APL    ECS   40RH   7
27/12   RICKEM   HPL    RTT   40RH  18

我需要的是这个:

ETA     Vessel   Line   POD   SZTP QTY
====    ======   ====   ===   ==== ===
26/12   MAEWIS   MAE    LIV   40RH  23
                        PBL   40RH  12
        APLMEN   APL    PTR   20DR  44
                        TRU   20DR  22
27/12   APLMEN   APL    ECS   40RH   7
        RICKEM   HPL    RTT   40RH  18

如果 ETA/VSL/LINE 中有很多 POD 和 SZTP，也许也会这样做

有没有办法做到这一点？

这是我的查询:

select to_char(vv.eta, 'DY-DD/MM') eta,
  a.linevv_vv_vsl_id||'/'||vv.out_voy_nbr vessel,
  a.linevv_line_id line,
  a.discharge_port_id1 pod,
    b.sztp_id sztp,
    b.qty qty
from
  service_orders a,
  service_order_items b,
  vessel_visits vv
where
   b.so_gkey = a.gkey and
   vv.vsl_id = a.linevv_vv_vsl_id and
   vv.out_voy_nbr = a.linevv_vv_out_voy_nbr and
   sub_type = 'VEPO' and
   ((vv.eta between sysdate and sysdate + 7) or (to_char(vv.ata, 'YYYY-MM-DD') =   to_char(sysdate, 'YYYY-MM-DD')))
order by to_char(vv.eta, 'YYYY-MM-DD')

我正在使用 Pentaho ETL Tool Kettle 来运行查询，并将数据流转换为 XML。所以，如果解决方案在 ETL 上，我也可以使用一些帮助来了解如何在那里进行。

非常感谢您的帮助。

最佳答案

我更喜欢使用所有键正确定义所有行。但是，您可以这样做，您只需要识别每个分组的第一行。

with t as (<your query here with `to_char(eta, 'YYYY-MM-DD') as eta_yyyymmdd` added>)
select (case when seqnum_eta = 1 then eta else '' end) as eta,
       (case when seqnum_vessel = 1 then vessel else '' end) as vessel,
       (case when seqnum_line = 1 then line else '' end) as line,
       pod, sztyp, qty
from (select t.*,
             row_number() over (partition by eta order by vessel, line, pod, sztp, qty) as seqnum_eta,
             row_number() over (partition by eta, vessel order by line, pod, sztp, qty) as seqnum_vessel,
             row_number() over (partition by eta, vessel, line order by pod, sztp, qty) as seqnum_line
      from t
     ) t
order by eta_yyyymmdd, t.vessel, t.line, t.pod, t.sztp, t.qty

关于sql - 避免在一组记录中重复值，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/14045782/