首页 > 数据库技术 > 详细

oracle行转列及分组排序

时间:2019-03-04 10:44:10      阅读:377      评论:0      收藏:0      [点我收藏+]

SELECT APC.APC_NAME,
APC.APC_ID,
PTC.PTI_NAME,
TEMP.PTI_ID,
TEMP.COUNTS,
ROW_NUMBER() OVER (PARTITION BY APC.APC_ID ORDER BY TEMP.PTI_ID) ROW_NUMBER
FROM (SELECT APC_ID,
NVL(INC_PTI_ID,8) PTI_ID,
COUNT(*) COUNTS
FROM (SELECT CCI.*
FROM CX_INTEGRITY_CONTENT_INFO CCI
WHERE PTC_ID = ‘1‘
AND INC_RTC_ID = ‘02‘)
GROUP BY APC_ID,
INC_PTI_ID) TEMP
LEFT JOIN PROFESSION_TYPE_CODE PTC ON TEMP.PTI_ID = PTC.PTI_ID
RIGHT JOIN ADMIN_PUNISH_CODE APC ON TEMP.APC_ID = APC.APC_ID;

技术分享图片

SELECT APC_ID,
APC_NAME,
WM_CONCAT(PTI_ID) PTI_ID,
WM_CONCAT(COUNTS) COUNTS
FROM (SELECT *
FROM (SELECT APC.APC_NAME,
APC.APC_ID,
PTC.PTI_NAME,
NVL(PTC.PTI_ID,8) PTI_ID,
NVL(TEMP.COUNTS,0) COUNTS
FROM (SELECT APC_ID,
INC_PTI_ID,
COUNT(*) COUNTS
FROM (SELECT CCI.*
FROM CX_INTEGRITY_CONTENT_INFO CCI
WHERE PTC_ID = ‘1‘
AND INC_RTC_ID = ‘02‘)
GROUP BY APC_ID,
INC_PTI_ID) TEMP
LEFT JOIN PROFESSION_TYPE_CODE PTC ON TEMP.INC_PTI_ID = PTC.PTI_ID
RIGHT JOIN ADMIN_PUNISH_CODE APC ON TEMP.APC_ID = APC.APC_ID))
GROUP BY APC_ID,
APC_NAME;

 

技术分享图片

 

oracle行转列及分组排序

原文:https://www.cnblogs.com/oath-keeper/p/10469148.html

(0)
(0)
   
举报
评论 一句话评论(0
关于我们 - 联系我们 - 留言反馈 - 联系我们:wmxa8@hotmail.com
© 2014 bubuko.com 版权所有
打开技术之扣,分享程序人生!