Oracle结果集中取最大的
--一个 ARP.KEY_ 对应多个 ARP.ID_ 和 ARP.VERSION_ ，--这里只取最大的 ARP.VERSION_ 对应的 ARP.ID_ 结果集SELECT T2.ARPID, T2.TSIIDFROM ( 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_ ) T2WHERE ROW_ = 1sql 列转行 列并行 wmsys.wm_concat
CREATE OR REPLACE VIEW ENC_CUS_TASKLIST_VIEW ASSELECT 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_KEYFROMSEC_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 TYCIWHERE 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);
页:
[1]