求一sq语句

显示全部楼层 · 2007-9-26 18:42:10

表one结构
name age
a 22
b 23
c 24
d 22
e 22
f 24
如何实现这样的结果
age count(*) content
22 3 a;d;e
23 1
b
24 2
cf
这样的语句怎么写啊？
select age,count(*),*** from one group by age***

千问 · 2007-9-26 18:42:10

最初由 linkadmin 发布
[B]表one结构
name age
a 22
b 23
c 24
d 22
e 22
f 24
如何实现这样的结果
age count(*) content
22 3 a;d;e
23 1
b
24 2
cf
这样的语句怎么写啊？
select age,count(*),*** from one group by age*** [/B]

得写个函数，然后在sql中引用这个函数

千问 · 2007-9-26 18:42:10

如何写啊头大了啊好郁闷啊

千问 · 2007-9-26 18:42:10

ding

千问 · 2007-9-26 18:42:10

zai ding

千问 · 2007-9-26 18:42:10

这个看上去象行列转换..好像论坛上有材料...找找看吧

千问 · 2007-9-26 18:42:10

最初由 Asda双心客发布
[B]这个看上去象行列转换..好像论坛上有材料...找找看吧 [/B]
对于不定数的进行转换肯定要写函数，不过对于LINKADMIN列出的数据还是可以用SQL处理的

SQL> select max(rk) from (select row_number() over(partition by age order by name) rk from tst_one);
MAX(RK)
----------
3
SQL> select a.age, b.cou ,a.name_list from
(select age,max(decode(rk,1,name,null))
||','||max(decode(rk,2,name,null))
||','||max(decode(rk,3,name,null)) name_list
from
(select age,name,row_number() over(partition by age order by name) rk
from tst_one )group by age) a,
(select age,count(*) cou from tst_one group by age) b
where a.age=b.age
AGECOU NAME_LIST
---- ---------- --------------------------------
22
3 A,B,C
24
1 D,,
26
2 E,F,

千问 · 2007-9-26 18:42:10

用函数

create or replace function get_name_list(tmp_c number) return varchar2 is
Result varchar2(4000);
begin
for cur in (select name from tst_one where age=tmp_c) loop
result :=result||' '||cur.name;
end loop;

result := rtrim(result,1);

return(Result);
end get_name_list;
select distinct a.*,get_name_list(b.age) from
(select age,count(*) cou from tst_one group by age) a, tst_one b
where a.age= b.age
AGECOU GET_NAME_LIST(B.AGE)
---- ---------- --------------------------------------------------------------------------------
22
3A B C
24
1D
26
2E F

千问 · 2007-9-26 18:42:10

你看这样行么？
SQL> select * from t;
NAMEAGE
---- ----
a22
b23
c24
d22
e22
f24
6 rows selected
SQL>
SQL> SELECT AGE, COUNT(AGE), SUBSTR(MAX(SYS_CONNECT_BY_PATH(NAME, ';')), 2) RET
2FROM (SELECT T.NAME,T.AGE,
3
ROW_NUMBER() OVER(PARTITION BY T.AGE ORDER BY ROWNUM) RN
4
FROM T)
5 START WITH RN = 1
6CONNECT BY PRIOR RN = RN - 1
7 AND AGE = PRIOR AGE
8 GROUP BY AGE
9/
AGE COUNT(AGE) RET
---- ---------- --------------------------------------------------------------------------------
22
3 a;d;e
23
1 b
24
2 c;f
SQL>

千问 · 2007-9-26 18:42:10

好家伙