在sql中从包含用分号分隔的数组的列中检索任意一个最大值

Question

我有这样的专栏

periodbal                  balancetype
-------------              -------------
0;15;11;-13;-16;20           ABS
22;25;-78;0;1                ABS
67;89;-36;83;90;55           ABS
…                            ACS
…

我想要找回

periodbal                  balancetype
---------                  -------------
20                            ABS
25                            ABS
90                            ABS

Answer 1

这里的想法是：

对于balancetype的每个值，从周期列中提取所有值，将它们转换为不同的行，然后计算每一行的平衡类型值的最大值。

即

PERIODBAL   BALANCETYPE     ID
0           ABS             1
15          ABS             1
11          ABS             1
-13         ABS             1
-16         ABS             1
20          ABS             1

用于ROW#2的拆分

22        ABS          2
25        ABS          2
-78       ABS          2
0         ABS          2
1         ABS          2
......

最后，获取MAX(PERIODBAL)并按列balancetype，ID对数据进行分组。

CREATE TABLE SYS.TEST
(
  PERIODBAL    VARCHAR2(50 BYTE),
  BALANCETYPE  VARCHAR2(5 BYTE)
)

INSERT INTO TEST VALUES ('0;15;11;-13;-16;20', 'ABS');
INSERT INTO TEST VALUES ('22;25;-78;0;1', 'ABS');
INSERT INTO TEST VALUES ('67;89;-36;83;90;55 ', 'ABS');
INSERT INTO TEST VALUES ('0;15;10;-13;-16;23', 'ACS');
INSERT INTO TEST VALUES ('0;14;11;-13;-16;25', 'ACS');

解决办法：

SELECT BALANCETYPE,MAX(BAL) AS PERIODBAL
FROM
(
SELECT SUB1.*, TRIM(REGEXP_SUBSTR( PERIODBAL, '[^;]+', 1, LVL)) BAL
FROM             (SELECT TEST.*, ROW_NUMBER() OVER (ORDER BY BALANCETYPE ) UNIQ_ID FROM TEST) SUB1 , 
                (SELECT LEVEL LVL FROM DUAL,(SELECT  MAX(LENGTH(REGEXP_REPLACE(PERIODBAL, '[^;]+')))+1 AS MAX_BAL FROM TEST)  TEMP
                 CONNECT BY LEVEL<= TEMP.MAX_BAL ) SUB2
WHERE LENGTH(REGEXP_REPLACE(PERIODBAL, '[^;]+'))+1>= SUB2.LVL
) GROUP BY BALANCETYPE, UNIQ_ID

产出：

BALANCETYPE PERIODBAL
ACS         23
ABS         20
ABS         25
ABS         90
ACS         25

希望能帮上忙谢谢。

Answer 2

在MySQL，您可以尝试一下

SELECT balanceType, MAX(CONVERT(SUBSTRING_INDEX(SUBSTRING_INDEX(t.periodBal, ';', n.n), ';', -1), SIGNED INTEGER)) maxValue
FROM SomeData t CROSS JOIN 
(
   SELECT a.N + b.N * 10 + 1 n
     FROM 
    (SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) a
   ,(SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) b
    ORDER BY n
) n
WHERE n.n <= 1 + (LENGTH(t.periodBal) - LENGTH(REPLACE(t.periodBal, ';', '')))
GROUP BY periodBal, balanceType
ORDER BY periodBal, maxValue;

查看它的实际操作：SQL Fiddle (为了检查某些边界情况，对数据进行了稍微调整)。

这是从SQL分隔逗号分隔行借来的

但是对于这种方法，我能找到的最古老的参考是从字符串创建MySQL集。