在oracle中串联一行之上的列的所有varchar值

s2j5cfk0 于 2023-06-29 发布在 Oracle

关注(0)|答案(3)|浏览(102)

我有一个用例，其中有一个存储一些字符的列，flg列和类别列。基于flg，如果flg是1，则对于第i行，resl应该是第i行上方的行的字符（通过“+”连接）和第i行Columnl值的连接。“第i行以上”意味着SEQNCE<SEQNCE（i）。
如果flg为零，则res 1 = Column 1。如果类别发生变化（例如：Category=2 Column 1 ='I'是一个新的集合），它应该从该行开始连接的过程。
例如，下面是名为“dmy_tbl”的表

LISTAGG是一个这样的选项，用于连接其他列的字符串组& LAG是用于获取行上方的行的选项，我已经尝试了下面的查询，但它没有产生预期的结果。我也无法决定如何划分类别。对于这个用例的任何建议都是非常受欢迎的。

select column1,catg,flg,SEQNCE,
case when ((flg=1 and res is null) or flg=0) then column1
else column1||'+'||res end as result
from
(select column1,catg,flg,SEQNCE,
case when flg=1 then lag(column1) 
over(order by catg,SEQNCE asc) else column1 end as res 
FROM dmy_tbl
order by catg,SEQNCE asc);

内部查询res列只提取上面的一行，不确定如何在内部查询中放入LISTAGG以提取上面的所有行

oracle

来源：https://stackoverflow.com/questions/76563909/concatenate-all-varchar-values-of-a-column-above-a-row-in-oracle

3条答案

按热度按时间

pkln4tw61#

可以使用MODEL子句：

SELECT column1,
       indicator,
       seqnce,
       category,
       CASE indicator
       WHEN 0
       THEN column1
       ELSE res
       END AS res1
FROM   dmy_tbl
MODEL
  PARTITION BY (category)
  DIMENSION BY (seqnce)
  MEASURES (
    column1,
    indicator,
    CAST(NULL AS VARCHAR2(4000)) AS res
  )  
  RULES (
    res[1] = column1[1],
    res[seqnce>1] = res[cv()-1] || '+' || column1[cv()]
  );

其中，对于样本数据：

CREATE TABLE dmy_tbl (column1, indicator, seqnce, category) AS
SELECT 'A', 1, 1, 1 FROM DUAL UNION ALL
SELECT 'B', 0, 2, 1 FROM DUAL UNION ALL
SELECT 'C', 0, 3, 1 FROM DUAL UNION ALL
SELECT 'D', 1, 4, 1 FROM DUAL UNION ALL
SELECT 'E', 1, 5, 1 FROM DUAL UNION ALL
SELECT 'F', 1, 6, 1 FROM DUAL UNION ALL
SELECT 'G', 0, 7, 1 FROM DUAL UNION ALL
SELECT 'H', 1, 8, 1 FROM DUAL UNION ALL
SELECT 'I', 1, 1, 2 FROM DUAL UNION ALL
SELECT 'J', 0, 2, 2 FROM DUAL UNION ALL
SELECT 'K', 1, 3, 2 FROM DUAL UNION ALL
SELECT 'L', 1, 4, 2 FROM DUAL;

输出：
| 指示器|序列|产品分类|RES1| RES1 |
| - -----|- -----|- -----|- -----| ------------ |
| 一个|一个|一个|一个| A |
| 0| 2|一个|B| B |
| 0|三个|一个|C类| C |
| 一个|4|一个|A+B+C+D| A+B+C+D |
| 一个|5个|一个|A+B+C+D+E| A+B+C+D+E |
| 一个|六|一个|A+B+C+D+E+F| A+B+C+D+E+F |
| 0|七个|一个|G级| G |
| 一个|八|一个|A+B+C+D+E+F+G+H| A+B+C+D+E+F+G+H |
| 一个|一个|2|我的| I |
| 0| 2| 2| J型| J |
| 一个|三个|2| I+J+K| I+J+K |
| 一个|4| 2| I+J+K+L| I+J+K+L |
fiddle

赞(0）回复(0）举报 2023-06-29

f87krz0w2#

如果您不关心是否涉及PL/SQL，那么一个简单的函数就可以完成这项工作。

SQL> CREATE OR REPLACE FUNCTION f_res (par_category IN NUMBER, par_seqnce NUMBER)
  2     RETURN VARCHAR2
  3  IS
  4     l_row   test%ROWTYPE;
  5     retval  VARCHAR2 (30);
  6  BEGIN
  7     SELECT *
  8       INTO l_row
  9       FROM test
 10      WHERE     category = par_category
 11            AND seqnce = par_seqnce;
 12
 13     IF l_row.flg = 0
 14     THEN
 15        retval := l_row.column1;
 16     ELSE
 17        SELECT LISTAGG (column1, '+') WITHIN GROUP (ORDER BY seqnce)
 18          INTO retval
 19          FROM test
 20         WHERE     category = par_category
 21               AND seqnce <= par_seqnce;
 22     END IF;
 23
 24     RETURN retval;
 25  END;
 26  /

Function created.

测试：

SQL>   SELECT t.*, f_res (t.category, t.seqnce) res1
  2      FROM test t
  3  ORDER BY t.category, t.seqnce;

C     SEQNCE        FLG   CATEGORY RES1
- ---------- ---------- ---------- ------------------------------
A          1          1          1 A
B          2          0          1 B
C          3          0          1 C
D          4          1          1 A+B+C+D
E          5          1          1 A+B+C+D+E
F          6          1          1 A+B+C+D+E+F
G          7          0          1 G
H          8          1          1 A+B+C+D+E+F+G+H
I          1          1          2 I
J          2          0          2 J
K          3          1          2 I+J+K
L          4          1          2 I+J+K+L

12 rows selected.

SQL>

赞(0）回复(0）举报 2023-06-29

zxlwwiss3#

在Oracle 12中，您可以使用MATCH_RECOGNIZE和LISTAGG：

SELECT MAX(CASE rn WHEN 1 THEN column1 END) AS column1,
       MAX(CASE rn WHEN 1 THEN indicator END) AS indicator,
       MAX(CASE rn WHEN 1 THEN seqnce END) AS seqnce,
       category,
       LISTAGG(column1, '+') WITHIN GROUP (ORDER BY column1) AS res1
FROM   dmy_tbl
MATCH_RECOGNIZE(
  PARTITION BY category
  ORDER BY seqnce DESC
  MEASURES
    COUNT(*) AS rn,
    MATCH_NUMBER() AS mn
  ALL ROWS PER MATCH
  AFTER MATCH SKIP TO NEXT ROW
  PATTERN ( zero | one any_row* )
  DEFINE
    zero AS indicator = 0,
    one  AS indicator = 1
)
GROUP BY category, mn
ORDER BY category, seqnce;

其中，对于样本数据：

CREATE TABLE dmy_tbl (column1, indicator, seqnce, category) AS
SELECT 'A', 1, 1, 1 FROM DUAL UNION ALL
SELECT 'B', 0, 2, 1 FROM DUAL UNION ALL
SELECT 'C', 0, 3, 1 FROM DUAL UNION ALL
SELECT 'D', 1, 4, 1 FROM DUAL UNION ALL
SELECT 'E', 1, 5, 1 FROM DUAL UNION ALL
SELECT 'F', 1, 6, 1 FROM DUAL UNION ALL
SELECT 'G', 0, 7, 1 FROM DUAL UNION ALL
SELECT 'H', 1, 8, 1 FROM DUAL UNION ALL
SELECT 'I', 1, 1, 2 FROM DUAL UNION ALL
SELECT 'J', 0, 2, 2 FROM DUAL UNION ALL
SELECT 'K', 1, 3, 2 FROM DUAL UNION ALL
SELECT 'L', 1, 4, 2 FROM DUAL;

赞(0）回复(0）举报 2023-06-29

我来回答

在oracle中串联一行之上的列的所有varchar值

3条答案

相关问题

热门标签

最新问答