SQL 实现全字段分组，每组取一条记录，记录满足：组内时间最大，组内不同类型数量求和
1 SELECT 2 TT.CLASS_ID AS "classId", 3 TT.TEMPLATE_ID AS "templateId" , 4 TT.MSG_CLASS_NAME AS "templateName", 5 TT.MSG_CLASS_CODE AS "templateCode", 6 TT.TEMPLATE_TITLE AS "templateTitle", 7 TT.TEMPLATE_TYPE AS "templateType", 8 TT.TEMPLATE_CONTENT AS "templateContent", 9 TT.MSG_SUBJECT AS "msgSubject", 10 TT.MSG_TITLE AS "msgTitle", 11 TT.MSG_CONTENT AS "msgContent", 12 TT.SEND_TIME AS "sendTime", 13 TT.COUNTNO AS "countNo", 14 TT.TOTAL AS "count" , 15 TT.ICON AS "icon" 16 FROM 17 ( 18 SELECT 19 TEMP.CLASS_ID, TEMP.TEMPLATE_ID , TEMP.ICON , TEMP.MSG_CLASS_NAME, TEMP.MSG_CLASS_CODE, TEMP.TEMPLATE_TITLE , TEMP.TEMPLATE_TYPE , TEMP.TEMPLATE_CONTENT , TEMP2.MSG_SUBJECT , TEMP2.MSG_TITLE , TEMP2.MSG_CONTENT , TEMP2.SEND_TIME , TEMP2.COUNTNO , SUM(TEMP2.COUNTNO) OVER (PARTITION BY TEMP.MSG_CLASS_CODE 20 ORDER BY 21 TEMP2.SEND_TIME DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS TOTAL, ROW_NUMBER() OVER (PARTITION BY TEMP.CLASS_ID 22 ORDER BY 23 TEMP2.SEND_TIME ASC) AS ROWNO 24 FROM 25 ( 26 SELECT 27 tamc.CLASS_ID , tamc.ICON , tamc.MSG_CLASS_NAME , tamc.MSG_CLASS_CODE , tamt.TEMPLATE_TYPE , tamt.TEMPLATE_ID , tamt.TEMPLATE_NAME , tamt.TEMPLATE_TITLE , tamt.TEMPLATE_CONTENT 28 FROM 29 MEAB.T_AP_MESSAGE_CLASS tamc 30 LEFT JOIN MEAB.T_AP_CLASS_TEMPLATE tact ON 31 tamc.CLASS_ID = tact.CLASS_ID 32 LEFT JOIN MEAB.T_AP_MESSAGE_TEMPLATE tamt ON 33 tamt.TEMPLATE_ID = tact.TEMPLATE_ID) TEMP 34 LEFT JOIN ( 35 SELECT 36 TT.MSG_SUBJECT , TT.MSG_CONTENT , TT.SEND_TIME , TT.BIZ_TYPE , TT.MSG_TITLE , COUNTNO 37 FROM 38 ( 39 SELECT 40 tam.MSG_SUBJECT , tam.MSG_CONTENT , tam.SEND_TIME , tam.BIZ_TYPE , tam.MSG_TITLE , ROW_NUMBER() OVER (PARTITION BY tam.BIZ_TYPE 41 ORDER BY 42 tam.SEND_TIME DESC) AS ROWNO , COUNT(1) OVER (PARTITION BY tam.BIZ_TYPE 43 ORDER BY 44 tam.SEND_TIME ASC) AS COUNTNO 45 FROM 46 MEAB.T_APP_MESSAGE tam 47 LEFT JOIN MEAB.T_APP_MESSAGE_SEND tams ON 48 tam.MSG_ID = tams.MESSAGE_ID 49 WHERE 50 tam.MSG_ORIGNAL = 'COL' 51 AND tam.MSG_STATUS = '01' 52 AND tams.SEND_STATUS = '01' 53 AND tams.SEND_OBJECT_NO = '971260') TT 54 WHERE 55 ROWNO = '1' ) TEMP2 ON 56 TEMP2.BIZ_TYPE = TEMP.TEMPLATE_TYPE) TT 57 WHERE 58 TT.ROWNO = 1
问题描述:
参考上图，加入有A,B,C多个大类型，每个类型下面有多个1,2,3等多个中类，中类下面有很多明细记录(数据在多张表)。要求实现：
1，按照大类分组，显示多个表的某些字段(字段不一定在分组参数),
2，中类分组后，统计每个中类分组的记录数count，
3，然后根据大类的分组求中类记录数和（统计大类的下，满足中类要求的所有记录数）。
运行结果：
按照classId进行分组，关联多个表，统计每个类型下面消息最新的记录，同时统计这类型消息的记录数有多少
关键函数:
1 ROW_NUMBER() OVER (PARTITION BY tam.BIZ_TYPE 2 ORDER BY 3 tam.SEND_TIME DESC) AS ROWNO , COUNT(1) OVER (PARTITION BY tam.BIZ_TYPE 4 ORDER BY 5 tam.SEND_TIME ASC) AS COUNTNO
ROWNO = '1' 取时间最大
1 SUM(TEMP2.COUNTNO) OVER (PARTITION BY TEMP.MSG_CLASS_CODE 2 ORDER BY 3 TEMP2.SEND_TIME DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS TOTAL, ROW_NUMBER() OVER (PARTITION BY TEMP.CLASS_ID 4 ORDER BY 5 TEMP2.SEND_TIME ASC) AS ROWNO
相同组内，包含不同类型求和
