Oracle结果集中取最大的

--  一个 ARP.KEY_ 对应多个 ARP.ID_ 和 ARP.VERSION_ ，
--  这里只取最大的 ARP.VERSION_ 对应的 ARP.ID_ 结果集
SELECT T2.ARPID, T2.TSIID
FROM (
     SELECT ARP.ID_ ARPID,
            ARP.KEY_,
            ARP.VERSION_,
            TSI.ID TSIID,
            ROW_NUMBER() OVER(PARTITION BY ARP.KEY_ ORDER BY ARP.VERSION_ DESC) AS ROW_
     FROM TBL_SALE_INFO TSI, ACT_RE_PROCDEF ARP
     WHERE TSI.DEL_FLG = 'N'
     AND TSI.WORKFLOW_ID = ARP.KEY_
     ) T2
WHERE ROW_ = 1

 

 

sql 列转行 列并行 wmsys.wm_concat

 

 

CREATE OR REPLACE VIEW ENC_CUS_TASKLIST_VIEW AS
SELECT TCI.COMPANY_NAME,
TBI.BRAND_NAME,
TAI.AREA_NAME,
TII.INDUST_NAME,
TYCI.CUS_NAME,
TYCI.USER_NAME,
TYCI.CUS_REQUIMENT,
TYCI.TELPHONE,
TYCI.MOBILE,
SU.USER_NAME CUSER_NAME,
decode (sign(TYCI.PRIORITY - 2), -1, '低',0,'中',1,'高') PRIORITY,
wmsys.wm_concat(TYCP.PRODUCT_NAME) PRODUCT_NAME,
TYCI.ID,
TYCI.BUSINESS_KEY

FROM
SEC_USER SU,
TBL_COMPANY_INFO TCI,
TBL_BRAND_INFO TBI,
TBL_AREA_INFO TAI,
TBL_INDUSTORY_INFO TII,
TBL_YZ_CUS_PRODUCT TYCP,
TBL_YZ_CUS_INFO TYCI
WHERE TYCI.DEL_FLG = 'N'
AND TYCI.DEL_FLG = TCI.DEL_FLG(+)
AND TYCI.DEL_FLG = TBI.DEL_FLG(+)
AND TYCI.DEL_FLG = TAI.DEL_FLG(+)
AND TYCI.DEL_FLG = TII.DEL_FLG(+)
AND TYCI.DEL_FLG = SU.DEL_FLG(+)
AND TYCI.DEL_FLG = TYCP.DEL_FLG(+)
AND TYCI.SEND_TO_USER = SU.USER_ID(+)
AND TYCI.COMPANY_ID = TCI.ID(+)
AND TYCI.BRAND_ID = TBI.ID(+)
AND TYCI.AREA_ID = TAI.ID(+)
AND TYCI.INDUST_ID = TII.ID(+)
AND TYCI.ID = TYCP.CUS_INFO_ID(+)

GROUP BY (TYCI.ID,TCI.COMPANY_NAME,TBI.BRAND_NAME,
            TAI.AREA_NAME,TII.INDUST_NAME, TYCI.CUS_NAME,
            TYCI.USER_NAME,TYCI.TELPHONE, TYCI.MOBILE,
            SU.USER_NAME, TYCI.CUS_REQUIMENT,TYCI.BUSINESS_KEY,TYCI.PRIORITY);