在OracleDB上使用select按需定制列

ehxuflar  于 2022-11-03  发布在  Oracle
关注(0)|答案(2)|浏览(302)

如果我有一个查询返回具有多个值的PARAM_VALUE,如
| 参数值|
| - -|
| 一、二、三、四、五、六|
如何使用SELECT按需返回多个列,以如下方式显示此数据
| 参数_值1|参数_值2|参数_值3|一个人。|
| - -|- -|- -|- -|
| 一个|2个|三个|一个人。|

inb24sb2

inb24sb21#

SQL(不仅仅是Oracle)需要已知的固定输出列数。因此,如果字符串中有固定的最大分隔值数,则可以使用字符串函数来查找子字符串:

SELECT param_value,
       CASE pos1
       WHEN 0
       THEN param_value
       ELSE SUBSTR(param_value, 1, pos1 - 1)
       END AS param_value1,
       CASE 
       WHEN pos1 = 0
       THEN NULL
       WHEN pos2 = 0
       THEN SUBSTR(param_value, pos1 + 1)
       ELSE SUBSTR(param_value, pos1 + 1, pos2 - pos1 - 1)
       END AS param_value2,
       CASE 
       WHEN pos2 = 0
       THEN NULL
       WHEN pos3 = 0
       THEN SUBSTR(param_value, pos2 + 1)
       ELSE SUBSTR(param_value, pos2 + 1, pos3 - pos2 - 1)
       END AS param_value3,
       CASE 
       WHEN pos3 = 0
       THEN NULL
       WHEN pos4 = 0
       THEN SUBSTR(param_value, pos3 + 1)
       ELSE SUBSTR(param_value, pos3 + 1, pos4 - pos3 - 1)
       END AS param_value4
FROM   (
  SELECT param_value,
         INSTR(param_value, ',', 1, 1) AS pos1,
         INSTR(param_value, ',', 1, 2) AS pos2,
         INSTR(param_value, ',', 1, 3) AS pos3,
         INSTR(param_value, ',', 1, 4) AS pos4,
         INSTR(param_value, ',', 1, 5) AS pos5
  FROM   table_name
);

或者,使用正则表达式,更简洁但速度更慢:

SELECT param_value,
       REGEXP_SUBSTR(param_value, '([^,]*)(,|$)', 1, 1, NULL, 1) AS param_value1,
       REGEXP_SUBSTR(param_value, '([^,]*)(,|$)', 1, 2, NULL, 1) AS param_value2,
       REGEXP_SUBSTR(param_value, '([^,]*)(,|$)', 1, 3, NULL, 1) AS param_value3,
       REGEXP_SUBSTR(param_value, '([^,]*)(,|$)', 1, 4, NULL, 1) AS param_value4
FROM   table_name;

其中,对于示例数据:

create table table_name (param_value) AS
  SELECT NULL FROM DUAL UNION ALL
  SELECT 'A' FROM DUAL UNION ALL
  SELECT 'A,B' FROM DUAL UNION ALL
  SELECT 'A,B,C' FROM DUAL UNION ALL
  SELECT 'A,B,C,D' FROM DUAL UNION ALL
  SELECT 'A,B,C,D,E' FROM DUAL;

两者输出:
| 参数值|参数_值1|参数_值2|参数_值3|参数_值4|
| - -|- -|- -|- -|- -|
| * 空值 | 空值 | 空值 | 空值 | 空值 *|
| A级|A级| * 空值 | 空值 | 空值 *|
| A、B| A级|B| * 空值 | 空值 *|
| A、B、C| A级|B| C类| * 空值 *|
| A、B、C、D| A级|B| C类|D级|
| A、B、C、D、E| A级|B| C类|D级|
fiddle

lfapxunr

lfapxunr2#

如果要为未知长度的列表生成值,请将值生成为行(而不是列):

SELECT param_value,
       item,
       CASE epos
       WHEN 0
       THEN SUBSTR(param_value, spos)
       ELSE SUBSTR(param_value, spos, epos - spos)
       END AS param
FROM   table_name t
       CROSS APPLY (
         SELECT LEVEL AS item,
                CASE LEVEL
                WHEN 1
                THEN 1
                ELSE INSTR(t.param_value, ',', 1, LEVEL - 1) + 1
                END AS spos,
                INSTR(t.param_value, ',', 1, LEVEL) AS epos
         FROM   DUAL
         CONNECT BY INSTR(t.param_value, ',', 1, LEVEL - 1) > 0
       ) b

其中,对于示例数据:

create table table_name (param_value) AS
  SELECT NULL FROM DUAL UNION ALL
  SELECT 'A' FROM DUAL UNION ALL
  SELECT 'A,B' FROM DUAL UNION ALL
  SELECT 'A,B,C' FROM DUAL UNION ALL
  SELECT 'A,B,C,D' FROM DUAL UNION ALL
  SELECT 'A,B,C,D,E' FROM DUAL;

输出:
| 参数值|项目|参数|
| - -|- -|- -|
| * 空值 *| 一个| * 空值 *|
| A级|一个|A级|
| A、B|一个|A级|
| A、B| 2个|B|
| A、B、C|一个|A级|
| A、B、C| 2个|B|
| A、B、C|三个|C类|
| A、B、C、D|一个|A级|
| A、B、C、D| 2个|B|
| A、B、C、D|三个|C类|
| A、B、C、D|四个|D级|
| A、B、C、D、E|一个|A级|
| A、B、C、D、E| 2个|B|
| A、B、C、D、E|三个|C类|
| A、B、C、D、E|四个|D级|
| A、B、C、D、E|五个|E级|
如果您希望将其作为列,则可以在用于访问数据库的任何第三方应用程序中转置数据集,因为它可能支持您希望的输出格式(而使用SQL生成动态输出将非常困难)。
fiddle

相关问题