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

相同组内，包含不同类型求和

 

posted @ 2023-02-17 09:57  未尝一死  阅读(580)  评论(0)    收藏  举报