如下:
c1 c2 c3 c4
x x x t1
x x x t2
x x x t3c4列的各项t1~t3值不同;c1,c2,c3 各列的值相同,我现在想使用group by分组成如下:c1 c2 c3 c4
x x x t1+t2+t3该如何实现?多谢指教了!
c1 c2 c3 c4
x x x t1
x x x t2
x x x t3c4列的各项t1~t3值不同;c1,c2,c3 各列的值相同,我现在想使用group by分组成如下:c1 c2 c3 c4
x x x t1+t2+t3该如何实现?多谢指教了!
这是大家再熟悉不过的写法,这是根据A列统计B列的和。
有些时候,B列是字符串类型的列,就像这样
A | B
---------------
a aa
a bb
a cc
139045771 33
139045771 44
139045771 55
a dd
我想要得到这样的结果:
139045771 33,44,55
a aa,bb,cc,dd
相当于想要把字符串“加和”解决的方式如下,麻烦了一点
先编译这个函数SumString
CREATE OR REPLACE FUNCTION SumString(
I_TableName IN VARCHAR2 ,
I_GroupColName IN VARCHAR2 ,
I_ResultColName IN VARCHAR2 ,
I_GroupColValue IN VARCHAR2 ,
I_Separator IN VARCHAR2
)
RETURN VARCHAR2 IS
TYPE T_Cur IS REF CURSOR ;
C_Cur T_Cur ;
V_Sql VARCHAR2(2000) ;
V_Result VARCHAR2(2000) ;
V_Tmp VARCHAR2(200) ;
V_Cnt NUMBER := 0 ;
BEGIN
V_Result := '' ;
V_Sql := 'SELECT '|| I_ResultColName ||' FROM '|| I_TableName ||' WHERE '|| I_GroupColName || ' = '''|| I_GroupColValue || '''' ;
OPEN C_Cur FOR V_Sql ;
LOOP
FETCH C_Cur INTO V_Tmp ;
EXIT WHEN C_Cur%NOTFOUND ;
IF V_Cnt = 0 THEN
V_Result := V_Tmp ;
ELSE
V_Result := V_Result || I_Separator || V_Tmp ;
END IF ;
V_Cnt := V_Cnt + 1 ;
END LOOP ;
CLOSE C_Cur ;
RETURN V_Result ;
END SUMSTRING; 再执行以下语句测试:
CREATE TABLE TestSumString( a VARCHAR2(10) , b VARCHAR2(10) ) ;
INSERT INTO TestSumString VALUES( 'a' , 'aa' ) ;
INSERT INTO TestSumString VALUES( 'a' , 'bb' ) ;
INSERT INTO TestSumString VALUES( 'a' , 'cc' ) ;
INSERT INTO TestSumString VALUES( '139045771' , '33' ) ;
INSERT INTO TestSumString VALUES( '139045771' , '44' ) ;
INSERT INTO TestSumString VALUES( '139045771' , '55' ) ;
INSERT INTO TestSumString VALUES( 'a' , 'dd' ) ;
COMMIT ;
SELECT A , SumString( 'TestSumString', 'a' , 'b' , a , ',' ) SUM_B FROM TestSumString GROUP BY A ;运行结果如下:
A SUM_B
---------- ---------------
139045771 33,44,55
a aa,bb,dffdfd,dd
和普通的GroupBy使用方法上没什么不同
SumString函数的5各参数的意思分别是:
1. 表名;
2. 你想Group BY的字段名
3. 你想sum的那个字段名
4. Group By字段的值
5. Sum字符串时的分隔符
from table
group by c1,c2,c3;
这是什么意思?能解释一下吗?
这是什么意思?能解释一下吗?
-------------------------------------------------------
就用group by的列名。
-------------------------------------------------
用group by的列名,但是需要用单引号括起来。
Result varchar2(4000);
temp varchar(1024);
arrayTemp dbms_sql.Varchar2_Table;
begin
EXECUTE IMMEDIATE sqlString BULK COLLECT INTO arrayTemp ;
if arrayTemp.count=0
then
return '';
end if;
Result:=arrayTemp(1);
for i in 2..arrayTemp.count LOOP
Result:=Result||splitSeg||arrayTemp(i);
end loop;
return(Result);
end sum_string;
select a,sum_string('select b from tableA where a='||a) from tableA group by a
FROM (
SELECT C1, C2, C3, C4,
ROW_NUMBER() OVER (PARTITION BY C1, C2, C3) AS CURR,
ROW_NUMBER() OVER (PARTITION BY C1, C2, C3) - 1 AS PREV
FROM tablname
)
START WITH CURR = 1
CONNECT BY PREV = PRIOR CURR AND C1 = PRIOR C1 AND C2 = PRIOR C2 AND C3 = PRIOR C3
GROUP BY C1, C2, C3
SELECT C1, C2, C3, LTRIM(MAX(SYS_CONNECT_BY_PATH(C4, '+')), '+') AS C4
FROM (
SELECT C1, C2, C3, C4,
ROW_NUMBER() OVER (PARTITION BY C1, C2, C3 ORDER BY C1, C2, C3) AS CURR,
ROW_NUMBER() OVER (PARTITION BY C1, C2, C3 ORDER BY C1, C2, C3) - 1 AS PREV
FROM tablname
)
START WITH CURR = 1
CONNECT BY PREV = PRIOR CURR AND C1 = PRIOR C1 AND C2 = PRIOR C2 AND C3 = PRIOR C3
GROUP BY C1, C2, C3
from table
group by c1,c2,c3;
这是什么意思?能解释一下吗?
----------
跟在where语句后面 用来过滤的
看函数就能猜出来了
-----
SELECT A , SumString( 'TestSumString', 'a' , 'b' , a , ',' ) SUM_B FROM TestSumString
-----
它具体的值是上面语句返回的结果集的每一行的A的值
c1,c2,c3,sum(c4) over()
from tabx
from table
group by c1,c2,c3;
from table
group by c1,c2,c3
select c1,c2,c3,sum(c4)
from [表名]
group by c1,c2,c3;
from table1
group by c1, c2, c3