一個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 去掉 應該怎么改
uj5u.com熱心網友回復：
這列的重復去掉，后面的列的值，怎么處理？uj5u.com熱心網友回復：
后面的保留下來uj5u.com熱心網友回復：
前面幾列重復的都可以去掉,后面三列保留下來uj5u.com熱心網友回復：
這應該叫聚合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
uj5u.com熱心網友回復：
不行啊,結果不對uj5u.com熱心網友回復：
忘了說了,RECEIVE_NUMBER這個欄位重復是因為一個RECEIVE_NUMBER對應了多個檔案,所以才會重復,實際上表里 RECEIVE_NUMBER欄位是不重復的uj5u.com熱心網友回復：
這列的重復去掉，后面的列的值，怎么處理？uj5u.com熱心網友回復：
后面3列保留,前面幾列重復的都可以去掉
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/32967.html
標籤：開發
下一篇：SQL 常見面試題