column_name
a
b
c
d
我想用一条sql语句达到下面这种效果
column_name
a,b,c,d
谢谢
5 个解决方案
#1
SQL> select * from test;
MARK
-----
88
96
84
89
SQL>
SQL> select mark||','||mark2||','||mark3||','||mark4 result from (
2 select mark,lead(mark,1,0)over(partition by rid order by mark) mark2,
3 lead(mark,2,0)over(partition by rid order by mark) mark3,
4 lead(mark,3,0)over(partition by rid order by mark) mark4
5 from ( select mark,1 rid from test)) where rownum=1 ;
RESULT
--------------------------------------------------------------------------------
84,88,89,96
MARK
-----
88
96
84
89
SQL>
SQL> select mark||','||mark2||','||mark3||','||mark4 result from (
2 select mark,lead(mark,1,0)over(partition by rid order by mark) mark2,
3 lead(mark,2,0)over(partition by rid order by mark) mark3,
4 lead(mark,3,0)over(partition by rid order by mark) mark4
5 from ( select mark,1 rid from test)) where rownum=1 ;
RESULT
--------------------------------------------------------------------------------
84,88,89,96
#2
可以,不过我的记录数是不确定的,我就不知道该怎么办!
还有lead,over这些用法不太清楚是什么意思,呵呵
还有lead,over这些用法不太清楚是什么意思,呵呵
#3
就一条语句应该不行吧,
我只能用cursor搞定
我只能用cursor搞定
#4
用游标最好,可以编写一个function实现,很简单的
#5
用储存过程来实现哦
#1
SQL> select * from test;
MARK
-----
88
96
84
89
SQL>
SQL> select mark||','||mark2||','||mark3||','||mark4 result from (
2 select mark,lead(mark,1,0)over(partition by rid order by mark) mark2,
3 lead(mark,2,0)over(partition by rid order by mark) mark3,
4 lead(mark,3,0)over(partition by rid order by mark) mark4
5 from ( select mark,1 rid from test)) where rownum=1 ;
RESULT
--------------------------------------------------------------------------------
84,88,89,96
MARK
-----
88
96
84
89
SQL>
SQL> select mark||','||mark2||','||mark3||','||mark4 result from (
2 select mark,lead(mark,1,0)over(partition by rid order by mark) mark2,
3 lead(mark,2,0)over(partition by rid order by mark) mark3,
4 lead(mark,3,0)over(partition by rid order by mark) mark4
5 from ( select mark,1 rid from test)) where rownum=1 ;
RESULT
--------------------------------------------------------------------------------
84,88,89,96
#2
可以,不过我的记录数是不确定的,我就不知道该怎么办!
还有lead,over这些用法不太清楚是什么意思,呵呵
还有lead,over这些用法不太清楚是什么意思,呵呵
#3
就一条语句应该不行吧,
我只能用cursor搞定
我只能用cursor搞定
#4
用游标最好,可以编写一个function实现,很简单的
#5
用储存过程来实现哦