修改sql语句

[想你的旧名字]

一个sql语句

SELECT * FROM (SELECT i.RECEIVE_NUMBER,i.ITEM_CODE,p.IDCARD_NO,p.NAME,i.BUSINESS_OBJECT_TYPE,f.DOCUMENT_NAME,f.FILE_PATH,f.FILE_NAME
FROM
ACCEPT_BUSINESS_INDEX i LEFT JOIN ACCEPT_PERSON p ON i.BUSINESS_OBJECT_CODE = p.SEQ_ID LEFT JOIN ACCEPT_BUSINESS_FILE f ON i.BSNUM = f.BSNUM
WHERE
i.BUSINESS_OBJECT_CODE = p.SEQ_ID AND i.BSNUM = f.BSNUM AND (i.STATE='99' OR i.STATE='100') AND f.TYPE='1' AND i.RECEIVE_NUMBER NOT IN (SELECT i.RECEIVE_NUMBER FROM ACCEPT_MATERIAL_DOSSIER m WHERE i.RECEIVE_NUMBER = m.BUSINESS_OBJECT_CODE )) WHERE ROWNUM <= 200


结果为


现在想把重复的 i.RECEIVE_NUMBER 去掉 应该怎么改

[卖水果的net]

这列的重复去掉，后面的列的值，怎么处理？

[想你的旧名字]

后面的保留下来

[想你的旧名字]

前面几列重复的都可以去掉,后面三列保留下来

[nayi_224]

这应该叫聚合 select RECEIVE_NUMBER, ITEM_CODE, IDCARD_NO, listagg(NAME) within group(order by 1), listagg(BUSINESS_OBJECT_TYPE) within group(order by 1) from tab group by RECEIVE_NUMBER, ITEM_CODE, IDCARD_NO

[想你的旧名字]

不行啊,结果不对

引用 4 楼 nayi_224 的回复:

这应该叫聚合 select RECEIVE_NUMBER, ITEM_CODE, IDCARD_NO, listagg(NAME) within group(order by 1), listagg(BUSINESS_OBJECT_TYPE) within group(order by 1) from tab group by RECEIVE_NUMBER, ITEM_CODE, IDCARD_NO

[想你的旧名字]

忘了说了,RECEIVE_NUMBER这个字段重复是因为一个RECEIVE_NUMBER对应了多个文件,所以才会重复,实际上表里 RECEIVE_NUMBER字段是不重复的

[AHUA1001]

这列的重复去掉，后面的列的值，怎么处理？

[想你的旧名字]

引用 7 楼 AHUA1001 的回复:

这列的重复去掉，后面的列的值，怎么处理？

后面3列保留,前面几列重复的都可以去掉