求种方法,怎么弄出我想要的 表Aaac001 aka12110000024 冠心病10000024 高血压三期10000024 恶性肿瘤怎么让其编程我想要的10000024 冠心病 高血压三期 恶性肿瘤此人可能申请多种病种(1-6) 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 SELECT aac001,REPLACE(WMSYS.WM_CONCAT(aka121),',',' ') AS aka121 FROM A GROUP BY aac001以上写法使用于ORACLE 10g create table t2(col1 varchar2(10),col2 varchar2(10)); insert into t2 values('001','vl1'); insert into t2 values('001','vl2'); insert into t2 values('001','vl3'); insert into t2 values('002','vl1'); insert into t2 values('002','vl2'); SELECT COL1, LTRIM(MAX(SYS_CONNECT_BY_PATH(COL2, ',')), ',') COL2 FROM (SELECT COL1, COL2, MIN(COL2) OVER(PARTITION BY COL1) COL2_MIN, (ROW_NUMBER() OVER(ORDER BY COL1, COL2)) + (DENSE_RANK() OVER(ORDER BY COL1)) NUMID FROM T2) START WITH COL2 = COL2_MIN CONNECT BY NUMID - 1 = PRIOR NUMID GROUP BY COL1 /*COL1 COL2 ---------- ----------------------------------------001 vl1,vl2,vl3 002 vl1,vl2 2 rows selected*/ 引用自dawugui前辈,/*标题:合并相同列的数据作者:爱新觉罗.毓华 时间:2008-05-05地点:广东深圳*//*情况说明NAME USERID张三 KB001张三 KB003李四 KB001李四 KB002李四 KB003用SQL实现表示如下:NAME USERID张三 KB001,KB003李四 KB001,KB002,KB003*/create table tb(NAME varchar2(10) , USERID varchar2(10))insert into tb values('张三' , 'KB001');insert into tb values('张三' , 'KB003');insert into tb values('李四' , 'KB001');insert into tb values('李四' , 'KB002');insert into tb values('李四' , 'KB003');--1select name,rtrim( max(decode(USERID , 'KB001' , USERID || ',' , '')) || max(decode(USERID , 'KB002' , USERID || ',' , '')) || max(decode(USERID , 'KB003' , USERID || ',' , '')),',') useridfrom tbgroup by name--2SELECT NAME, ltrim(MAX(sys_connect_by_path(userid , ',')) , ',') useridFROM (SELECT NAME , userid , row_number() over(PARTITION BY NAME ORDER BY userid) rn , rownum prn FROM tb)START WITH rn = 1CONNECT BY prn - 1 = PRIOR prn AND NAME = PRIOR NAMEGROUP BY NAMEORDER BY NAME;drop table tb/*NAME USERID ---------- ---------------------------------李四 KB001,KB002,KB003 张三 KB001,KB003 2 rows selected*/ Oracle中trigger的几个问题 想问个大数据量跨分区查询慢问题 求救 大虾看看 为什么说 这不是group by 表达式?? 再求一查询 oracle 怎样查看数据库在做什么事情 ,会导致cpu过高? 谢谢 to_char的返回类型 求一巨难的sql语句 求助一个问题(MERGE INTO) schema a可以访问到表索引,schema b却不可以,要怎么处理呢?? 关于多个子查询,其中一个为空,那所有数据都没有,该如何解决该问题 用纯C语言写了一个HtmlParse(网页分析器)外带采集功能,大小只有200K(免费+开源+操作示意图) Oracle 字符集
SELECT aac001,REPLACE(WMSYS.WM_CONCAT(aka121),',',' ') AS aka121 FROM A GROUP BY aac001以上写法使用于ORACLE 10g
insert into t2 values('001','vl1');
insert into t2 values('001','vl2');
insert into t2 values('001','vl3');
insert into t2 values('002','vl1');
insert into t2 values('002','vl2'); SELECT COL1, LTRIM(MAX(SYS_CONNECT_BY_PATH(COL2, ',')), ',') COL2
FROM (SELECT COL1,
COL2,
MIN(COL2) OVER(PARTITION BY COL1) COL2_MIN,
(ROW_NUMBER() OVER(ORDER BY COL1, COL2)) +
(DENSE_RANK() OVER(ORDER BY COL1)) NUMID
FROM T2)
START WITH COL2 = COL2_MIN
CONNECT BY NUMID - 1 = PRIOR NUMID
GROUP BY COL1 /*
COL1 COL2
---------- ----------------------------------------
001 vl1,vl2,vl3
002 vl1,vl2
2 rows selected
*/
/*
标题:合并相同列的数据
作者:爱新觉罗.毓华
时间:2008-05-05
地点:广东深圳
*//*情况说明
NAME USERID
张三 KB001
张三 KB003
李四 KB001
李四 KB002
李四 KB003用SQL实现表示如下:
NAME USERID
张三 KB001,KB003
李四 KB001,KB002,KB003
*/create table tb(NAME varchar2(10) , USERID varchar2(10))
insert into tb values('张三' , 'KB001');
insert into tb values('张三' , 'KB003');
insert into tb values('李四' , 'KB001');
insert into tb values('李四' , 'KB002');
insert into tb values('李四' , 'KB003');--1
select name,rtrim(
max(decode(USERID , 'KB001' , USERID || ',' , '')) ||
max(decode(USERID , 'KB002' , USERID || ',' , '')) ||
max(decode(USERID , 'KB003' , USERID || ',' , '')),',') userid
from tb
group by name--2
SELECT NAME, ltrim(MAX(sys_connect_by_path(userid , ',')) , ',') userid
FROM (SELECT NAME , userid , row_number() over(PARTITION BY NAME ORDER BY userid) rn , rownum prn FROM tb)
START WITH rn = 1
CONNECT BY prn - 1 = PRIOR prn AND NAME = PRIOR NAME
GROUP BY NAME
ORDER BY NAME;drop table tb/*
NAME USERID
---------- ---------------------------------
李四 KB001,KB002,KB003
张三 KB001,KB003
2 rows selected
*/