SELECT
f.nowdate AS "nowdate",
f.DAYOFWEEK "dayofweek",
f.WEEKOFMONTH AS "weekofmonth",
f.YEARMONTH "yearmonth",
COUNT (1) AS "messageNum",
LTRIM (
MAX (
SYS_CONNECT_BY_PATH (f.C2 || ',' || f.state, '|') -----------SYS_CONNECT_BY_PATH函数主要作用是可以把一个父节点下的所有子节点通过某个字符进行区分,然后连接在一个列中显示。
) KEEP (
DENSE_RANK LAST -------------DENSE_RANK是排序策略 FIRST/LAST就是对数据进行筛选了
ORDER BY
f.pnum
),
'|'
f.nowdate AS "nowdate",
f.DAYOFWEEK "dayofweek",
f.WEEKOFMONTH AS "weekofmonth",
f.YEARMONTH "yearmonth",
COUNT (1) AS "messageNum",
LTRIM (
MAX (
SYS_CONNECT_BY_PATH (f.C2 || ',' || f.state, '|') -----------SYS_CONNECT_BY_PATH函数主要作用是可以把一个父节点下的所有子节点通过某个字符进行区分,然后连接在一个列中显示。
) KEEP (
DENSE_RANK LAST -------------DENSE_RANK是排序策略 FIRST/LAST就是对数据进行筛选了
ORDER BY
f.pnum
),
'|'
) AS "message"