如何复制我的sql结果?

问题描述

我想根据该表的数量从表中的一行中获得多个结果.

ID |数数一个 |12乙 |138

所以我希望查询的输出是 12 次 A(无论是否有计数器)和 138 次 B.我已经看到通过在数据库上添加计数表的解决方案，但我不能这样做.

解决方案

您可以使用相关的分层查询:

SELECT t.id, r.COLUMN_VALUE, t.cntFROM table_name t交叉连接桌子(投(多组(选择级别从双按级别连接 

或递归子查询分解子句:

WITH row_generator ( id, lvl, cnt ) AS (选择 id, 1, cnt从表名联合所有SELECT id, lvl + 1, cntFROM row_generator凡 lvl 

所以对于测试数据:

CREATE TABLE table_name ( ID, cnt ) ASSELECT 'A', 12 from DUAL UNION ALL选择 'B', 138 从双

这两种解决方案都会输出:

身份证 |COLUMN_VALUE |碳纳米管:- |-----------: |——:一个 |1 |12一个 |2 |12一个 |3 |12...一个 |10 |12一个 |11 |12一个 |12 |12乙 |1 |138乙 |2 |138乙 |3 |138乙 |4 |138...乙 |136 |138乙 |137 |138乙 |138 |138

db<>fiddle 这里

I want to get multiple results from one line in table based on a number of that table.

ID | count
A  | 12
B  | 138

So I want the output of the query to be 12 times A (no matter if with a counter or not) and 138 times B.I have seen a solution by adding a count table on the DB, but I can't do that.

解决方案

You can use a correlated hierarchical query:

SELECT t.id, r.COLUMN_VALUE, t.cnt
FROM   table_name t
       CROSS JOIN
       TABLE(
         CAST(
           MULTISET(
             SELECT LEVEL
             FROM   DUAL
             CONNECT BY LEVEL <= t.cnt
           )
           AS SYS.ODCINUMBERLIST
         )
       ) r;

or a recursive sub-query factoring clause:

WITH row_generator ( id, lvl, cnt ) AS (
  SELECT id, 1, cnt
  FROM   table_name
UNION ALL
  SELECT id, lvl + 1, cnt
  FROM   row_generator
  WHERE  lvl < cnt
)
SELECT *
FROM   row_generator
ORDER BY id, lvl

So for the test data:

CREATE TABLE table_name ( ID, cnt ) AS
SELECT 'A',  12 FROM DUAL UNION ALL
SELECT 'B', 138 FROM DUAL

Both these solutions would output:

db<>fiddle here

这篇关于如何复制我的sql结果?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！