我正在使用oracle 12c数据库。我正在尝试用子查询方法通过listagg选择不同的值。但是我得到了一个错误"ORA-01427:单行子查询返回多行01427. 00000 -“单行子查询返回多行”“。
下面是我的代码:
SELECT DISTINCT nr.sd_id,
( select listagg(nr.r_num,' ') within group (ORDER BY Nlssort(nr.r_num, 'NLS_SORT=BINARY_CI'))
OVER ( PARTITION BY nr.sd_id, nr.rid, nr.rr_id) r_num_group
from (select distinct r_num from sg_nr) )
FROM sg_nr nr;发布于 2020-06-03 19:27:03
请使用以下查询,
SELECT DISTINCT nr.sd_id, listagg(nr.r_num,' ') within group (ORDER BY
Nlssort(nr.r_num, 'NLS_SORT=BINARY_CI')
OVER ( PARTITION BY nr.sd_id, nr.rid, nr.rr_id) r_num_group FROM sg_nr nr;发布于 2020-06-03 19:33:52
您不需要windows子句(OVER)。只需在内部子查询中使用WHERE子句,如下所示:
SELECT DISTINCT NR.SD_ID,
( SELECT LISTAGG(NR.R_NUM,' ') WITHIN GROUP(
ORDER BY NLSSORT( NR.R_NUM, 'NLS_SORT=BINARY_CI'))
FROM (
SELECT DISTINCT R_NUM
FROM SG_NR NRIN
WHERE NRIN.SD_ID = NR.SD_ID
AND NRIN.RID = NR.RID
AND NRIN.RR_ID = NR.RR_ID)) AS R_NUM_GROUP
FROM SG_NR NR;最好的方法是使用子查询作为内部视图,并将其与主表连接,如下所示:
SELECT NR.SD_ID, NRIN.R_NUM_GROUP
FROM SG_NR NR
JOIN (
SELECT NRIN.SD_ID, NRIN.RID, NRIN.RR_ID,
LISTAGG(NRIN.R_NUM, ' ') WITHIN GROUP(
ORDER BY NLSSORT( NRIN.R_NUM, 'NLS_SORT=BINARY_CI' )
) R_NUM_GROUP
FROM ( SELECT DISTINCT NRIN.R_NUM, NRIN.SD_ID, NRIN.RID, NRIN.RR_ID
FROM SG_NR NRIN) NRIN
GROUP BY NRIN.SD_ID, NRIN.RID, NRIN.RR_ID
) NRIN ON NRIN.SD_ID = NR.SD_ID
AND NRIN.RID = NR.RID
AND NRIN.RR_ID = NR.RR_ID;发布于 2020-06-03 20:17:01
性能透视图可能如下所示,因为您已经在使用相同的表SG_NR。然后使用带有MATERIALIZE提示的LISTAGG函数。
WITH NR AS (SELECT /*+MATERIALIZE*/
* FROM SG_NR)
SELECT NR.SD_ID, NR2.R_NUM_GROUP
FROM NR
INNER JOIN
( SELECT NR2.SD_ID,
NR2.RID,
NR2.RR_ID,
LISTAGG (
NR2.R_NUM,
' ')
WITHIN GROUP (ORDER BY
NLSSORT (NR2.R_NUM, 'NLS_SORT=BINARY_CI'))
R_NUM_GROUP
FROM ( SELECT NR2.R_NUM,
NR2.SD_ID,
NR2.RID,
NR2.RR_ID
FROM NR NR2
GROUP BY NR2.R_NUM,
NR2.SD_ID,
NR2.RID,
NR2.RR_ID) NR2
GROUP BY NR2.SD_ID, NR2.RID, NR2.RR_ID) NR2
ON NR2.SD_ID = NR.SD_ID
AND NR2.RID = NR.RID
AND NR2.RR_ID = NR.RR_IDhttps://stackoverflow.com/questions/62171629
复制相似问题