SQL 查询从双返回 N 行

发布于 2024-08-16 10:04:56 字数 223 浏览 7 评论 0原文

我想编写一个 SQL 查询，它接受一个绑定变量（例如：NUM），其输出由一列和一个列组成。 :NUM 行数，每行都有其行号。即，如果我们将 :NUM 作为 7 传递，则输出应该是：

VAL
====
1
2
3
4
5
6
7

查询中不应该有任何实际的数据库表，并且不应使用 PL/SQL 代码。即在查询中只能使用双重

有什么办法可以实现这一点吗？

原文

I want to write a SQL query which accepts a bind variable (say :NUM) and its output consists of one column & :NUM number of rows, each row having its row number. i.e. if we pass :NUM as 7, the output should be:

VAL
====
1
2
3
4
5
6
7

There shouldn't be any actual DB tables in query and no PL/SQL code should be used. i.e. only dual should be used in the query

Is there any way to achieve this?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

伊面 2024-08-23 10:04:57

WITH cte_numbers(n) 
AS (
    SELECT 0
    UNION  ALL
    SELECT n + 1
    FROM  cte_numbers
    WHERE n < 10
)
SELECT n
FROM cte_numbers;

返回的行
0
1
2
3
4
5
6
7
8
9
10

WITH cte_numbers(n) 
AS (
    SELECT 0
    UNION  ALL
    SELECT n + 1
    FROM  cte_numbers
    WHERE n < 10
)
SELECT n
FROM cte_numbers;

Returned Rows
0
1
2
3
4
5
6
7
8
9
10

回复收藏 0 原文

甜｀诱少女 2024-08-23 10:04:57

取决于数据库，可以使用各种方法。

PostgreSQL 有一个很好的功能 -- 系列。

为了得到你想要的东西：

SELECT * FROM generate_series(1, NUM);

Depends on database various method can be used.

PostgreSQL has a nice feature -- series.

To get what you want just want:

SELECT * FROM generate_series(1, NUM);

回复收藏 0 原文

梦里人 2024-08-23 10:04:56

您可以使用：

 WHERE ROWNUM <= :NUM

...但表必须包含等于或大于绑定变量中的限制的行。此链接演示了 Oracle 中的各种行号生成技术。

使用CONNECT BY，Oracle 10g+：

SELECT LEVEL
  FROM DUAL
CONNECT BY LEVEL <= :NUM

由monojohnny确认可以使用绑定变量。尽管支持 CONNECT BY 语法，但尝试在 Oracle 9i 上运行会导致 ORA-01436 错误。

我唯一没有 100% 同意的是 CONNECT BY 是否会接受绑定变量的限制。

参考：

整数级数生成器 - CONNECT BY LEVEL 方法

You could use:

 WHERE ROWNUM <= :NUM

...but the table has to contain row equal or greater to the limit in the bind variable. This link demonstrates various row number generation techniques in Oracle.

Using CONNECT BY, Oracle 10g+:

SELECT LEVEL
  FROM DUAL
CONNECT BY LEVEL <= :NUM

Confirmed by monojohnny that the bind variable can be used. Attempts to run on Oracle 9i, though CONNECT BY syntax is supported results in an ORA-01436 error.

The only thing I'm not 100% on is if the CONNECT BY will accept the limit from the bind variable.

Reference:

Integer Series Generators - CONNECT BY LEVEL Method

回复收藏 0 原文

最单纯的乌龟 2024-08-23 10:04:56

尝试这样的事情：

SELECT 1 AS Val FROM dual
UNION ALL SELECT 2 FROM dual
UNION ALL SELECT 3 FROM dual
UNION ALL SELECT 4 FROM dual
UNION ALL SELECT 5 FROM dual
UNION ALL SELECT 6 FROM dual
UNION ALL SELECT 7 FROM dual;

虽然很乱，但可以解决问题。

编辑：啊 - 你需要传递一个变量来让你知道要走多高...

那么像这样的东西怎么样：

SELECT t1.Val + t2.Val * 2 + t3.Val * 4 + t4.Val * 8 AS Val
FROM
(
SELECT 0 AS Val FROM dual
UNION ALL SELECT 1 FROM dual
) AS t1, 
(
SELECT 0 AS Val FROM dual
UNION ALL SELECT 1 FROM dual
) AS t2, 
(
SELECT 0 AS Val FROM dual
UNION ALL SELECT 1 FROM dual
) AS t3, 
(
SELECT 0 AS Val FROM dual
UNION ALL SELECT 1 FROM dual
) AS t4
WHERE t1.Val + t2.Val * 2 + t3.Val * 4 + t4.Val * 8 <= 7;

好的...再次编辑，现在使用WITH：

WiTH 
A0 AS (SELECT 0 as N FROM DUAL UNION ALL SELECT 0 FROM DUAL),
A1 AS (SELECT 0 as N FROM A0, A0 AS B),
A2 AS (SELECT 0 as N FROM A1, A1 AS B),
A3 AS (SELECT 0 as N FROM A2, A2 AS B),
A4 AS (SELECT 0 as N FROM A3, A3 AS B),
A5 AS (SELECT 0 as N FROM A4, A4 AS B),
A6 AS (SELECT 0 as N FROM A5, A5 AS B),
Nums AS (SELECT ROW_NUMBER() OVER (ORDER BY N) AS Val FROM A6)
SELECT *
FROM Nums
WHERE Val <= :NUM
;

Try something like:

SELECT 1 AS Val FROM dual
UNION ALL SELECT 2 FROM dual
UNION ALL SELECT 3 FROM dual
UNION ALL SELECT 4 FROM dual
UNION ALL SELECT 5 FROM dual
UNION ALL SELECT 6 FROM dual
UNION ALL SELECT 7 FROM dual;

It's messy, but it'll do the trick.

Edited: Ah - you need to pass in a variable to let you know how high to go...

So how about something like:

SELECT t1.Val + t2.Val * 2 + t3.Val * 4 + t4.Val * 8 AS Val
FROM
(
SELECT 0 AS Val FROM dual
UNION ALL SELECT 1 FROM dual
) AS t1, 
(
SELECT 0 AS Val FROM dual
UNION ALL SELECT 1 FROM dual
) AS t2, 
(
SELECT 0 AS Val FROM dual
UNION ALL SELECT 1 FROM dual
) AS t3, 
(
SELECT 0 AS Val FROM dual
UNION ALL SELECT 1 FROM dual
) AS t4
WHERE t1.Val + t2.Val * 2 + t3.Val * 4 + t4.Val * 8 <= 7;

Ok... editing again, now using WITH:

WiTH 
A0 AS (SELECT 0 as N FROM DUAL UNION ALL SELECT 0 FROM DUAL),
A1 AS (SELECT 0 as N FROM A0, A0 AS B),
A2 AS (SELECT 0 as N FROM A1, A1 AS B),
A3 AS (SELECT 0 as N FROM A2, A2 AS B),
A4 AS (SELECT 0 as N FROM A3, A3 AS B),
A5 AS (SELECT 0 as N FROM A4, A4 AS B),
A6 AS (SELECT 0 as N FROM A5, A5 AS B),
Nums AS (SELECT ROW_NUMBER() OVER (ORDER BY N) AS Val FROM A6)
SELECT *
FROM Nums
WHERE Val <= :NUM
;

回复收藏 0 原文

芯好空 2024-08-23 10:04:56

我没有想出这个答案[所以请确保所有投票都以正确的方式进行！！]，这只是我基于“OMG Ponies”的测试笔记[谁不确定该方法是否适用于绑定变量]上面供参考：

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> var num_rows number
SQL> begin select 20 into :num_rows from dual;
  2  end;
  3  /

PL/SQL procedure successfully completed.

SQL> select level from dual
  2  connect by level <=:num_rows;

     LEVEL
----------
         1
         2
         3
         4
 ...

I didn't come up with this answer [ so make sure any votes go the right way!!] , it just my testing notes based on 'OMG Ponies' [who wasn't sure whether the method would work with binding variable] above for reference:

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> var num_rows number
SQL> begin select 20 into :num_rows from dual;
  2  end;
  3  /

PL/SQL procedure successfully completed.

SQL> select level from dual
  2  connect by level <=:num_rows;

     LEVEL
----------
         1
         2
         3
         4
 ...

回复收藏 0 原文

千仐 2024-08-23 10:04:56

不通过连接查询

WITH num(n) as(select 1 from dual union all
select n+1 from num where n <= :num_limit)
select * from num

Query without connect by

WITH num(n) as(select 1 from dual union all
select n+1 from num where n <= :num_limit)
select * from num

回复收藏 0 原文

勿忘初心 2024-08-23 10:04:56

我标记这个社区维基是因为它实际上并没有满足您对没有表的要求，但是我们在安装数据库时要做的第一件事就是为此目的创建一组表。

包含大量整数的表（例如，-99999 到 99999）。
包含从过去 10 年到未来 10 年的每个日期的表格（不断添加到每个月并偶尔进行修剪）。
包含一天中每个小时的表。

通过这样做，我们以（最少且便宜的）磁盘空间为代价，大大降低了大量查询的复杂性并提高了速度。

你应该认真考虑一下。除了维护日期表之外，不需要太多维护。

回复收藏 0 原文

著墨染雨君画夕 2024-08-23 10:04:56

另一种解决方案需要一些 PL/SQL 创建一个函数来返回包含行的集合...不像 select level from double connect by level <= :b1 方法那么简单，但它是在某些情况下很有用：

1) 创建一个数字表对象类型（在本例中为 number_tbl ）：

create or replace type number_tbl as table of number;

2) 创建一个函数，该函数将接收要生成的行数，然后返回一个带有结果的 number_tbl 对象：

create or replace function get_rows( i_num_rows number ) return number_tbl as
  t number_tbl := number_tbl();
begin
  if i_num_rows < 1 then
    return null;
  end if;

  t.extend( i_num_rows );

  for i in 1..i_num_rows loop
    t(i) := i;
  end loop;

  return t;
end get_rows;

3)使用 table( ... ) 函数从函数中进行选择，将 number_tbl 对象转换为可选择的对象：

select * from table( cast ( get_rows( :b1 ) as number_tbl ) );

Another solution would require some PL/SQL to create a function to return a collection with the rows... Not as simple as the select level from dual connect by level <= :b1 approach, but it's useful in a few situations:

1) Create a number table object type ( number_tbl, in this example ) :

create or replace type number_tbl as table of number;

2) Create a function that will receive the number of rows to be generated, and then return a number_tbl object with the results:

create or replace function get_rows( i_num_rows number ) return number_tbl as
  t number_tbl := number_tbl();
begin
  if i_num_rows < 1 then
    return null;
  end if;

  t.extend( i_num_rows );

  for i in 1..i_num_rows loop
    t(i) := i;
  end loop;

  return t;
end get_rows;

3) select from your function using the table( ... ) function to turn your number_tbl object into something selectable:

select * from table( cast ( get_rows( :b1 ) as number_tbl ) );

回复收藏 0 原文

蓝戈者 2024-08-23 10:04:56

连接真是一件美妙的事情。它可以帮助您使用双表中可用的一组数据生成多行。这可以帮助您为虚拟数据生成大量行。例如

insert into test select a.* from test1 a,(select * from dual connect by level <=100000) b;

，您可以执行类似

示例 2 的操作：您想要打印从 1 到 10 的数字的平方和立方。

SQL> select level "No", power(level,2) "Square", power(level,3) "Cube"  from dual     connect by level <= 10;

    No     Square       Cube
---------- ---------- ----------
     1          1          1
     2          4          8
     3          9         27
     4         16         64
     5         25        125
     6         36        216
     7         49        343
     8         64        512
     9         81        729
    10        100       1000

因此，您可以按照您想要的任何形式操作它。这是从双表返回多行的方法。
参考文献： http://www.oraclebin.com/2012 /12/multipe-rows-from-dual-table.html

connect by is such a wonderful thing. It helps you generated multiple rows with a single set of data available in dual table. This can help you generate huge no of rows for your dummy data. For example

insert into test select a.* from test1 a,(select * from dual connect by level <=100000) b;

or you can do something like this

Example 2 : You want to print square and cube of numbers from 1 to 10.

SQL> select level "No", power(level,2) "Square", power(level,3) "Cube"  from dual     connect by level <= 10;

    No     Square       Cube
---------- ---------- ----------
     1          1          1
     2          4          8
     3          9         27
     4         16         64
     5         25        125
     6         36        216
     7         49        343
     8         64        512
     9         81        729
    10        100       1000

Hence you can manipulate it in whatever form you want. This is how you can return multiple rows from dual table.
References : http://www.oraclebin.com/2012/12/multipe-rows-from-dual-table.html

回复收藏 0 原文

听风念你 2024-08-23 10:04:56

另一种方法是使用 XQuery 范围表达式，eg：

select column_value from xmltable(:a||' to '||:b);

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

这种解决方案非常灵活，eg：

select column_value from xmltable('5 to 10, 15 to 20');

 5
 6
 7
 8
 9
10
15
16
17
18
19
20

Another way is to use an XQuery range expression, e.g.:

select column_value from xmltable(:a||' to '||:b);

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

This solution is quite flexible, e.g.:

select column_value from xmltable('5 to 10, 15 to 20');

 5
 6
 7
 8
 9
10
15
16
17
18
19
20

回复收藏 0 原文

~没有更多了~

关于作者

原谅过去的我

暂无简介

0 文章

0 评论

23 人气

关注发私信

友情链接

文江博客

SQL 查询从双返回 N 行

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

评论（10）

关于作者

相关话题

热门标签

推荐作者

内心激荡

JSmiles

赏烟花じ飞满天

左秋

迪街小绵羊

瞳孔里扚悲伤

友情链接

SQL 查询从双返回 N 行

如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

评论（10）

关于作者

相关话题

热门标签

推荐作者

内心激荡

JSmiles

赏烟花じ飞满天

左秋

迪街小绵羊

瞳孔里扚悲伤

友情链接

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。