一个DQL问题,请支招

显示全部楼层 · 2013-6-9 10:15:34

winxp+oracle9i
create table a(
id number(9)
)
insert into a values(1);
insert into a values(2);
insert into a values(3);
insert into a values(4);
insert into a values(5);
insert into a values(6);
insert into a values(7);
insert into a values(8);
insert into a values(9);

如何得到结果
1,2,3,4,5,6,7,8,9
谢谢!
我一般用过程实现的,不知道能否用SQL实现?请指教.

千问 · 2013-6-9 10:15:34

很简单，请搜索行列转换
ps：什么叫 dql

千问 · 2013-6-9 10:15:34

insert into a
select rownum from dual connect by rownumselect t.t from seer t;
T
----------
22
23
24
25
26
27
6 rows selected
SQL>
SQL> SELECT ltrim(max(Sys_Connect_By_Path(q.t, ',')),',')
2FROM (SELECT Rownum a,t
3
FROM Seer) q
4STARTWITH q.t = '22'
5CONNECT BY Prior a =a-1
6;
LTRIM(MAX(SYS_CONNECT_BY_PATH(
--------------------------------------------------------------------------------
22,23,24,25,26,27
SQL>

千问 · 2013-6-9 10:15:34

最初由 myfriend2010 发布
[B]SQL> select t.t from seer t;
T
----------
22
23
24
25
26
27
6 rows selected
SQL>
SQL> SELECT ltrim(max(Sys_Connect_By_Path(q.t, ',')),',')
2FROM (SELECT Rownum a,t
3
FROM Seer) q
4STARTWITH q.t = '22'
5CONNECT BY Prior a =a-1
6;
LTRIM(MAX(SYS_CONNECT_BY_PATH(
--------------------------------------------------------------------------------
22,23,24,25,26,27
SQL> [/B]
就是这个，谢谢！！

千问 · 2013-6-9 10:15:34

千问 · 2013-6-9 10:15:34

升序排列不可以么