ORACLE数据库 wm_concat排序、使用过多导致 ORA-01467: sort key too long

项目场景：

项目有个动态增删用户关联数据的功能，新做的数据库中没有加入自增主键，只能通过用户ID进行分组，一次查出所有数据，在前端通过split函数进行切割重绘

---

问题描述：

数据查询时配合max和wm_concat、over函数使用的确可以有效的排序，但查询的字段一多就会报sort key too long错误
例如：

//当max函数超出2个时就会报错
select
	FLDUSERID as userid,
	MAX( macs )AS macs,
	MAX( locks ) AS locks,
	max(ispid) as ispisd
	FROM
		(
		SELECT
			FLDUSERID,
			wmsys.wm_concat ( FLDMAC ) over ( partition BY FLDUSERID ORDER BY FLDMAC ) macs,
			wmsys.wm_concat ( FLDLOCK ) over ( partition BY FLDUSERID ORDER BY FLDMAC ) locks,
			wmsys.wm_concat ( FLDISPID ) over ( partition BY FLDUSERID ORDER BY FLDMAC ) isPid,
			wmsys.wm_concat ( FLDISPSUFFIX ) over ( partition BY FLDUSERID ORDER BY FLDMAC ) isPSufFix
		FROM
			tbluserbindmacs
			 where FLDUSERID = '$$'
		)
	GROUP BY
		FLDUSERID

---

原因分析：

网上关于这个问题的讨论很少，但就报错的信息来说，应该是使用了太多的组函数了

---

解决方案：

使用 WITH A AS 这个函数对SQL进行拆分，例如一次要查出4个数据以上的话就把3个数据为一个单位进行拆分。
例如：

WITH a AS (
select
	FLDUSERID as userid,
	REPLACE (MAX( macs ), ',', ';' ) AS macs,
	MAX( locks ) AS locks
	FROM
		(
		SELECT
			FLDUSERID,
			wmsys.wm_concat ( FLDMAC ) over ( partition BY FLDUSERID ORDER BY FLDMAC ) macs,
			wmsys.wm_concat ( FLDLOCK ) over ( partition BY FLDUSERID ORDER BY FLDMAC ) locks
		FROM
			tbluserbindmacs
		)
	GROUP BY
		FLDUSERID
	) , b AS (
	SELECT
		FLDUSERID as useridb,
		MAX( isPid ) AS isPid,
		REPLACE (MAX( isPSufFix ), ',', ';' ) AS isPSufFix
	FROM
		(
		SELECT
			FLDUSERID,
			wmsys.wm_concat ( FLDISPID ) over ( partition BY FLDUSERID ORDER BY FLDMAC ) isPid,
			wmsys.wm_concat ( FLDISPSUFFIX ) over ( partition BY FLDUSERID ORDER BY FLDMAC ) isPSufFix
		FROM
			tbluserbindmacs
		)
	GROUP BY
		FLDUSERID
	)
	//这里做查询动作，可以将限制条件放在这里 将a、b表的主键做对等限制可以让两个with的数据关联起来
	select * from a,b where a.userid = b.useridband a.userid = #{userId}

文中使用的方法有：oracle 多行合并 wmsys.wm_concat （有排序问题）、WITH a AS （类似创建临时表）