一张表tabl1 如下:
name class 小张 学生
小张 学生会
小王 老师
小李 学生
我想查询出来结果,就有多重身份的就把group用一个符号链接,最好是自定义函数,功能类似sum函数。name class小张 学生;学生会
小王 老师
小李 学生
name class 小张 学生
小张 学生会
小王 老师
小李 学生
我想查询出来结果,就有多重身份的就把group用一个符号链接,最好是自定义函数,功能类似sum函数。name class小张 学生;学生会
小王 老师
小李 学生
group by name
group by name
group by name
select name,replace(wm_concat(class),',',';') class from temp group by name
select name,repalec(wm_concat(class),',',';') from tablename
group by name
select sys_connect_by_path(class,';') from t1 where connect_by_isleaf=1
connect by prior rn=rn=-1 and prior name=name
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Prod
PL/SQL Release 10.1.0.2.0 - Production
CORE 10.1.0.2.0 Production
TNS for 32-bit Windows: Version 10.1.0.2.0 - Production
NLSRTL Version 10.1.0.2.0 - Production
SQL> select wm_concat(empno) from emp;
WM_CONCAT(EMPNO)
--------------------------------------------------------------------------------
7369,7499,7521,7566,7654,7698,7782,7788,7839,7844,7876,7900,7902,7934
SQL>
是不是安装不全的原因。
ORA-00904: "WM_CONCAT": .......
--Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
--PL/SQL Release 10.2.0.1.0 - Production
--"CORE 10.2.0.1.0 Production"
--TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
--NLSRTL Version 10.2.0.1.0 - Production
--数据
create table tb(name varchar2(20), class varchar2(20));insert into tb values('小张', '学生');
insert into tb values('小张', '学生会');
insert into tb values('小王', '老师');
insert into tb values('小李', '学生');select name,replace(wm_concat(class),',',';') class from tb
group by name ;
--结果
NAME CLASS
小李 学生
小王 老师
小张 学生;学生会
SELECT c1, SUBSTR (MAX (SYS_CONNECT_BY_PATH (CLASS, ';')), 2) NAME
FROM (SELECT name c1,CLASS,rn, LEAD (rn) OVER (PARTITION BY name ORDER BY rn) rn1
FROM (SELECT name ,CLASS, ROW_NUMBER () OVER (ORDER BY class) rn
FROM tabll))
START WITH rn1 IS NULL
CONNECT BY rn1 = PRIOR rn
GROUP BY c1;
--针对你这个需求,有三种方法可以实现
--方法一、使用wmsys.wm_concat Oracle版本要求10g及以上
--方法二、使用sys_connect_by_path Oracle版本在9i及以下
--方法三、使用自定义函数 --我来给你写给自定义函数实现的例子
--查看员工表中部门为10的员工姓名--自定义函数实现
create or replace function my_concat(p_deptno in integer)
return varchar2 --记住:参数和返回值里的数据类型都不用定义长度
is
result varchar2(4000); --定义变量,记住Oracle中定义变量不需要
begin
for temp_cursor in (select ename from emp where deptno=p_deptno) loop --此处在游标FOR循环中使用查询对应部门的所有员工
result :=result || temp_cursor.ename || ',';
end loop;
result := rtrim(result,','); --去掉最后一个空格
return result;
end;
SELECT DISTINCT deptno,my_concat(deptno) name FROM emp WHERE deptno=10;结果:
DEPTNO NAME
-----------------------
30 ALLEN,WARD,MARTIN,BLAKE,TURNER,JAMES,BLACK,MAXTON
1、 打开SQL/plus 输入 SYSTEM/IQORA 数据库IQORA
2、 输入@@加上路径(如D:\oracle\iqms\RDBMS\ADMIN\owminst.plb),回车即可。
3、 如果不成功则有可能是需要新建用户WMSYS
官方是这样说的:
WMSSYS is used to store all the metadata information for Oracle Workspace Manager. This user was introduced in Oracle9i and (like most Oracle9i supporting accounts) is locked by default. The user account is locked because we want the password to be public but restrict access to the account to the SYS schema. So, to unlock the account, DBA privileges are required. This post will show you on how to use the method WMSSYS.WM_CONCAT to convert the row and columns in data table.Installing Workspace Manager is fairly straightforward. If the database was created with the Database Configuration Assistant (DBCA), then Workspace Manager will automatically be installed. When you manually create a database using your own scripts, you must install the Workspace Manager by connecting to the database as the SYS user and running the script: $ORACLE_HOME/rdbms/admin/owminst.plb.
It is worth mentioning what is involved when Workspace Manager is installed. The script first creates a user called WMSYS that will be used to hold all Workspace Manager objects (Packages, Views, etc.). The default password for WMSYS is WMSYS, but the account is locked when it gets created. (There is really no need to unlock and login to this account.). The WMSYS user will be assigned the following roles:
CONNECT
RESOURCE
CREATE PUBLIC SYNONYM
DROP PUBLIC SYNONYM
CREATE ROLE
爱上你了。。
我按照这个做了,虽然报了一大堆错误,不过wm_concat函数好使了,帅气
lz给加分