对偶的sql查询

发布于 2024-09-25 11:52:57 字数 217 浏览 3 评论 0原文

这：

    select * 
      from dual  
connect by level <= i

...将返回结果为

1
2
3
...
i

可以修改查询以获取逐行结果吗？ IE

1 2 3 .....i

原文

This:

    select * 
      from dual  
connect by level <= i

...will return result as

1
2
3
...
i

Can the query be modified to get result row wise? i.e

1 2 3 .....i

分享到QQ

分享到微博

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

发布评论

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

小清晰的声音 2024-10-02 11:52:57

您需要的功能称为“透视”——它将行数据转换为列数据。相反的称为“unpivot”。有 PIVOT/UNPIVOT 语法，但直到 Oracle 11g 才可用。

在 Oracle 9i+ 上，使用 CASE 语句，而在此之前您需要使用 DECODE 构建逻辑，以便值按列显示。下面是一个解决 Oracle 9i+ 上 LEVEL 限制为 5 的示例：

    SELECT MAX(CASE WHEN LEVEL = 1 THEN LEVEL END) AS one,
           MAX(CASE WHEN LEVEL = 2 THEN LEVEL END) AS two,
           MAX(CASE WHEN LEVEL = 3 THEN LEVEL END) AS three,
           MAX(CASE WHEN LEVEL = 4 THEN LEVEL END) AS four,
           MAX(CASE WHEN LEVEL = 5 THEN LEVEL END) AS five
      FROM DUAL
CONNECT BY LEVEL <= 5

需要聚合函数 MAX 将结果集“展平”为单行/记录。

如果您到目前为止，您会想“但我不想为每一列指定 - 我希望它基于 i 是动态的......”。有两个问题：

列数有限制 - 请参阅 ORA-01792 表或视图中的最大列数为 1000。这适用于 8i 和 8i。无论如何，9i。坦率地说，需要生成这么多列会引发严重的问题，即您要尝试对结果集执行什么操作（是否有更好的方法）...
动态需求意味着使用动态SQL

The functionality you're after is called "pivot"--it's converting row data into columnar data. The opposite is called "unpivot". There is PIVOT/UNPIVOT syntax, but it isn't available until Oracle 11g.

On Oracle 9i+, CASE statements are used while prior to that you need to use DECODE to construct the logic so the values come out in columns. Here's an example for addressing if the limit to LEVEL is five on Oracle 9i+:

    SELECT MAX(CASE WHEN LEVEL = 1 THEN LEVEL END) AS one,
           MAX(CASE WHEN LEVEL = 2 THEN LEVEL END) AS two,
           MAX(CASE WHEN LEVEL = 3 THEN LEVEL END) AS three,
           MAX(CASE WHEN LEVEL = 4 THEN LEVEL END) AS four,
           MAX(CASE WHEN LEVEL = 5 THEN LEVEL END) AS five
      FROM DUAL
CONNECT BY LEVEL <= 5

The aggregate function MAX is necessary to "flatten" the resultset into a single row/record.

If you got this far, you're thinking "but I don't want to have to specify for every column--I want it to be dynamic based on i...". There's two issues:

There is a limit to the number of columns - see ORA-01792 maximum number of columns in a table or view is 1000. That goes for 8i & 9i, anyways. And frankly, needing to generate that many columns raises serious questions about what you're trying to do with the resultset, if there might be a better approach...
Dynamic requirements means using dynamic SQL

回复收藏 0 原文

孤独患者 2024-10-02 11:52:57

试试这个：

select trim(both ',' from sys_connect_by_path(level, ',')) as string
  from dual
 where level = 100
connect by level <= 100

更新：测试输出：

SQL> 
SQL>  select trim(both ',' from sys_connect_by_path(level, ',')) as string
  2     from dual
  3    where level = 20
  4   connect by level <= 20
  5  /

STRING
--------------------------------------------------------------------------------
1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20

另一个更新：我想我在发布之前没有充分阅读所有评论，抱歉。 :) 如果您需要选择每个数字作为单独的列，那么是的，请参阅 OMG Ponies 的答案 - 它是枢轴或动态 SQL。 :)

Try this:

select trim(both ',' from sys_connect_by_path(level, ',')) as string
  from dual
 where level = 100
connect by level <= 100

update: Testing the output:

SQL> 
SQL>  select trim(both ',' from sys_connect_by_path(level, ',')) as string
  2     from dual
  3    where level = 20
  4   connect by level <= 20
  5  /

STRING
--------------------------------------------------------------------------------
1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20

another update: Guess I haven't read all comments well enough before posting, sorry. :) If you need to select each number as a separate column, then yes, see, OMG Ponies' answer - it's either pivot or dynamic SQL. :)

回复收藏 0 原文

~没有更多了~