请高手解决sql语句问题！急

显示全部楼层 · 2009-10-9 08:28:00

表如下

id varchar2,val varchar2,bs varchar2)
idval bs
1 a m
2 b m
2 c n
3 d n
怎么用sql语句得到如下所示：
idval bs
1 a m
2 bc mn
3 d n

千问 · 2009-10-9 08:28:00

select id,
replace(wmsys.wm_concat(val), ',', '') val,
replace(wmsys.wm_concat(bs), ',', '') bs
from t
group by id;

千问 · 2009-10-9 08:28:00

楼上的高人，刚才需求有点问题能不能这样？
idval bs
1 a m
2 bc m
2 cd n
3 d n
怎么用sql语句得到如下所示：
idval bs
1 a m
2 bcd m|n
3 d n

千问 · 2009-10-9 08:28:00

WITH TEMP AS
(
SELECT 1 ID,'a' VAL,'m' BS FROM DUAL
UNION
SELECT 2 ID,'bc' VAL,'m' BS FROM DUAL
UNION
SELECT 2 ID,'cd' VAL,'n' BS FROM DUAL
UNION
SELECT 3 ID,'d' VAL,'n' BS FROM DUAL
)
SELECT REPLACE(WMSYS.WM_CONCAT(VAL1),',','') VAL,REPLACE(WMSYS.WM_CONCAT(BS),',','|') BS FROM
(
SELECT T.*,NVL(LTRIM(VAL,SUBSTR(REVERSE(LAG(VAL,1,NULL) OVER(PARTITION BY ID ORDER BY VAL)),1,1)),VAL) VAL1 FROM TEMP T
) GROUP BY ID
VAL
BS
a
m
bcd
m|n
d
n

千问 · 2009-10-9 08:28:00

如果是如下的数据呢？
idval bs
1 a m
2 bcgf m
2 cdcg n
3 dn

千问 · 2009-10-9 08:28:00

原帖由 chen222246lei 于 2009-3-19 16:31 发表
WITH TEMP AS
(
SELECT 1 ID,'a' VAL,'m' BS FROM DUAL
UNION
SELECT 2 ID,'bc' VAL,'m' BS FROM DUAL
UNION
SELECT 2 ID,'cd' VAL,'n' BS FROM DUAL
UNION
SELECT 3 ID,'d' VAL,'n' BS FROM DUAL
)
SELECT REPLACE(WMSYS.WM_CONCAT(VAL1),',','') VAL,REPLACE(WMSYS.WM_CONCAT(BS),',','|') BS FROM
(
SELECT T.*,NVL(LTRIM(VAL,SUBSTR(REVERSE(LAG(VAL,1,NULL) OVER(PARTITION BY ID ORDER BY VAL)),1,1)),VAL) VAL1 FROM TEMP T
) GROUP BY ID
VAL
BS
a
m
bcd
m|n
d
n
有更简单的写法么？。。。

千问 · 2009-10-9 08:28:00

学习学习

千问 · 2009-10-9 08:28:00

oracle 9i里面没有这个函数??????????WMSYS.WM_CONCAT()

千问 · 2009-10-9 08:28:00

有数据库表:
select * from ano_dept;
DEPTNO DNAME
LOC
------ -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES
CHICAGO
40 OPERATIONS BOSTON
50 Operations Boston
使用bulk connect into与rowtype结合时,总是出错:
declare
cursor c1 is select * from ano_dept;
type v_dept_type is table of ano_dept%rowtype;
v_dept v_dept_type;
begin
open c1;
fetch c1 bulk collect into v_dept;
for i in 1..v_dept.count loop
dbms_output.put_line(v_dept(i).deptno||' '||v_dept(i).dname);
end loop;
close c1;
end;

ORA-06550: 第 8 行, 第 29 列:
PLS-00597: INTO 列表中的表达式 'V_DEPT'类型错误
ORA-06550: 第 8 行, 第 2 列:
PL/SQL: SQL Statement ignored
而我采用type,使用正常:
declare
cursor c1 is select dname from ano_dept;
type v_dept_name_type is table of ano_dept.dname%type;
v_dept_name v_dept_name_type;
begin
open c1;
fetch c1 bulk collect into v_dept_name;
for i in 1..v_dept_name.count loop
dbms_output.put_line(v_dept_name(i));
end loop;
close c1;
end;

我的oracle版本是 9.0.1.1.1 ,请各位高手多多指教,解释问题出在哪了

千问 · 2009-10-9 08:28:00

误操作