SQL的拼写

--下面是些基础数据 sys@10.0.116.143> desc t Name Null? Type ----------------------------------------------------- -------- ----------- COL1 VARCHAR2(3) COL2 VARCHAR2(3) COL3 VARCHAR2(3) COL4 VARCHAR2(3) sys@10.0.116.143> select * from t; COL COL COL COL --- --- --- --- A01 B01 C01 D01 A01 B02 C02 D02 A02 B03 C03 D03 A02 B04 C04 D04 A02 B05 C05 D05 A03 B06 C06 D06 6 rows selected. --下面这部分实际上就是创建一个聚合函数sumc sys@10.0.116.143> create or replace type TypeSumVarchar as object 2 ( 3 sum VARCHAR2(100), 4 static function ODCIAggregateInitialize(sctx IN OUT TypeSumVarchar) 5 return number, 6 member function ODCIAggregateIterate(self IN OUT TypeSumVarchar, 7 value IN varchar2) return number, 8 member function ODCIAggregateTerminate(self IN TypeSumVarchar, 9 returnValue OUT VARCHAR2, flags IN number) return number, 10 member function ODCIAggregateMerge(self IN OUT TypeSumVarchar, 11 ctx2 IN TypeSumVarchar) return number 12 ); 13 / Type created. sys@10.0.116.143> CREATE OR REPLACE TYPE BODY TYPESUMVARCHAR is 2 static function ODCIAggregateInitialize(sctx IN OUT TypeSumVarchar) 3 return number is 4 begin 5 sctx := TypeSumVarchar(''); 6 return ODCIConst.Success; 7 end; 8 9 member function ODCIAggregateIterate(self IN OUT TypeSumVarchar, value IN VARCHAR2) 10 return number is 11 begin 12 self.sum:=self.sum||','||value; 13 return ODCIConst.Success; 14 end; 15 16 member function ODCIAggregateTerminate(self IN TypeSumVarchar, returnValue OUT 17 VARCHAR2, flags IN number) return number is 18 begin 19 returnValue := substr(self.sum,2); 20 return ODCIConst.Success; 21 end; 22 23 member function ODCIAggregateMerge(self IN OUT TypeSumVarchar, ctx2 IN 24 TypeSumVarchar) return number is 25 begin 26 return ODCIConst.Success; 27 end; 28 end; 29 / Type body created. sys@10.0.116.143> CREATE OR REPLACE FUNCTION SUMC (input VARCHAR2) RETURN VARCHAR2 2 AGGREGATE USING TypeSumVarchar; 3 / Function created. --编写sql实现功能 sys@10.0.116.143> column col2 format a11 sys@10.0.116.143> select a.col1,a.col2,b.col3,b.col4 from 2 (select col1,sumc(col2) col2 from t group by col1) a, 3 (select col1,col3,col4 from (select col1,col3,col4,row_number() over(partition by col1 order by col1,col2) rn from t) where rn = 1) b 4 where a.col1 = b.col1; COL COL2 COL COL --- ----------- --- --- A01 B01,B02 C01 D01 A02 B03,B04,B05 C03 D03 A03 B06 C06 D06