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

Question

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

我有一个 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 上，我也可以使用一些帮助来了解如何在 ETL 上实现。

非常感谢您的帮助。

Answer 1

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

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