NUM_SCRN_SIZE_ID NUM_CHNL_BACK_ID NUM_ICON_ID NUM_W NUM_H VC2_BACK_DIR VC2_ICON_DIR
1 1 20 24 240 320 1188787663-0aifusenkobe-4.jpg 1188787663-0aifusenkobe-4.jpg
2 1 20 25 240 320 1188787663-0aifusenkobe-4.jpg 1188787321-0aifusenkobe-3.jpg
3 1 20 26 240 320 1188787663-0aifusenkobe-4.jpg 0910-iverson-jiaodiantu-1.jpg
4 1 20 27 240 320 1188787663-0aifusenkobe-4.jpg 0910-chu-iverson-jiaodiantu.jpg
我想要的效果是NUM_SCRN_SIZE_ID NUM_CHNL_BACK_ID NUM_ICON_ID NUM_W NUM_H VC2_BACK_DIR VC2_ICON_DIR1,VC2_ICON_DIR2,VC2_ICON_DIR3,VC2_ICON_DIR41 1 20 24 240 320 1188787663-0aifusenkobe-4.jpg 1188787663-0aifusenkobe-4.jpg 1188787321-0aifusenkobe-3.jpg
1188787321-0aifusenkobe-2.jpg 1188787321-0aifusenkobe-1.jpg 人都晕死了!
1 1 20 24 240 320 1188787663-0aifusenkobe-4.jpg 1188787663-0aifusenkobe-4.jpg
2 1 20 25 240 320 1188787663-0aifusenkobe-4.jpg 1188787321-0aifusenkobe-3.jpg
3 1 20 26 240 320 1188787663-0aifusenkobe-4.jpg 0910-iverson-jiaodiantu-1.jpg
4 1 20 27 240 320 1188787663-0aifusenkobe-4.jpg 0910-chu-iverson-jiaodiantu.jpg
我想要的效果是NUM_SCRN_SIZE_ID NUM_CHNL_BACK_ID NUM_ICON_ID NUM_W NUM_H VC2_BACK_DIR VC2_ICON_DIR1,VC2_ICON_DIR2,VC2_ICON_DIR3,VC2_ICON_DIR41 1 20 24 240 320 1188787663-0aifusenkobe-4.jpg 1188787663-0aifusenkobe-4.jpg 1188787321-0aifusenkobe-3.jpg
1188787321-0aifusenkobe-2.jpg 1188787321-0aifusenkobe-1.jpg 人都晕死了!
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
*/
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
*/
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
*/
(
CURRENTSTR VARCHAR2(4000),
CURRENTSEPRATOR VARCHAR2(8),
STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT STRCAT_TYPE)
RETURN NUMBER,
MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT STRCAT_TYPE,
VALUE IN VARCHAR2) RETURN NUMBER,
MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF IN STRCAT_TYPE,
RETURNVALUE OUT VARCHAR2,
FLAGS IN NUMBER)
RETURN NUMBER,
MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT STRCAT_TYPE,
CTX2 IN STRCAT_TYPE) RETURN NUMBER
);
CREATE OR REPLACE TYPE BODY STRCAT_TYPE IS
STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT STRCAT_TYPE)
RETURN NUMBER IS
BEGIN
SCTX := STRCAT_TYPE('', ',');
RETURN ODCICONST.SUCCESS;
END;
MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT STRCAT_TYPE,
VALUE IN VARCHAR2) RETURN NUMBER IS
BEGIN
IF SELF.CURRENTSTR IS NULL THEN
SELF.CURRENTSTR := VALUE;
ELSE
SELF.CURRENTSTR := SELF.CURRENTSTR || CURRENTSEPRATOR || VALUE;
END IF;
RETURN ODCICONST.SUCCESS;
END;
MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF IN STRCAT_TYPE,
RETURNVALUE OUT VARCHAR2,
FLAGS IN NUMBER) RETURN NUMBER IS
BEGIN
RETURNVALUE := SELF.CURRENTSTR;
RETURN ODCICONST.SUCCESS;
END;
MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT STRCAT_TYPE,
CTX2 IN STRCAT_TYPE) RETURN NUMBER IS
BEGIN
IF CTX2.CURRENTSTR IS NULL THEN
SELF.CURRENTSTR := SELF.CURRENTSTR;
ELSIF SELF.CURRENTSTR IS NULL THEN
SELF.CURRENTSTR := CTX2.CURRENTSTR;
ELSE
SELF.CURRENTSTR := SELF.CURRENTSTR || CURRENTSEPRATOR ||
CTX2.CURRENTSTR;
END IF;
RETURN ODCICONST.SUCCESS;
END;
END;CREATE OR REPLACE FUNCTION strcat (input VARCHAR2) RETURN VARCHAR2 PARALLEL_ENABLE AGGREGATE USING strcat_type;
select NUM_SCRN_SIZE_ID NUM_CHNL_BACK_ID NUM_ICON_ID NUM_W NUM_H VC2_BACK_DIR ,strcat (VC2_ICON_DIR ) from table where group by NUM_SCRN_SIZE_ID .. 此段代码忘记出处了,原创作者bs我吧