有两个表t1,t2t1
id name1
-----------------
a n1a
b n1bt2
t1id name2 num1 num2 num3 string1 string2
--------------------------------------------------------------------------
a n2a1 10 20 30 str1a1 str2a1
a n2a2 20 30 40 str1a2 str2a2
b n2b1 30 40 50 str1b1 str2b1
b n2b2 40 50 60 str1b2 str2b2
b n2b3 50 60 70 str1b3 str2b3想得到下面的查询结果:
name1 name2 num1 num2 num3 string1 string2
------------------------------------------------------------------------------
n1a n2a1 10 20 30 str1a1 str2a1
n2a2 20 30 40 str1a2 str2a2
小计 30 50 70
n1b n2b1 30 40 50 str1b1 str2b1
n2b2 40 50 60 str1b2 str2b2
n2b3 50 60 70 str1b3 str2b3
小计 120 150 180
总计 150 200 250
请问怎样才能实现?请各位指点一二,先谢谢了!
id name1
-----------------
a n1a
b n1bt2
t1id name2 num1 num2 num3 string1 string2
--------------------------------------------------------------------------
a n2a1 10 20 30 str1a1 str2a1
a n2a2 20 30 40 str1a2 str2a2
b n2b1 30 40 50 str1b1 str2b1
b n2b2 40 50 60 str1b2 str2b2
b n2b3 50 60 70 str1b3 str2b3想得到下面的查询结果:
name1 name2 num1 num2 num3 string1 string2
------------------------------------------------------------------------------
n1a n2a1 10 20 30 str1a1 str2a1
n2a2 20 30 40 str1a2 str2a2
小计 30 50 70
n1b n2b1 30 40 50 str1b1 str2b1
n2b2 40 50 60 str1b2 str2b2
n2b3 50 60 70 str1b3 str2b3
小计 120 150 180
总计 150 200 250
请问怎样才能实现?请各位指点一二,先谢谢了!
一种是用分析函数,应该是ROLLUP吧,你可以去查一下它的用法。
CREATE TABLE t1(ID VARCHAR2(10),name1 VARCHAR(10));
INSERT INTO t1 VALUES('a','n1a');
INSERT INTO t1 VALUES('b','n1b');DROP TABLE t2;
CREATE TABLE t2(t1id VARCHAR(10),name2 VARCHAR(10),num1 INT,num2 INT,num3 INT,string1 Varchar(10),string2 Varchar(10));
INSERT INTO t2 VALUES('a','n2a1',10,20,30,'str1a1','str2a1');
INSERT INTO t2 VALUES('a','n2a2',20,30,40,'str1a2','str2a2');
INSERT INTO t2 VALUES('b','n2b1',30,40,50,'str1b1','str2b1');
INSERT INTO t2 VALUES('b','n2b2',40,50,60,'str1b2','str2b2');
INSERT INTO t2 VALUES('b','n2b3',50,60,70,'str1b3','str2b3');
---------------------------------------------------------------------------
SELECT case when lag(name1) over(partition by name1 order by name2) = name1
then nvl2(name2, null, '小计')
else nvl(name1, '合计') end name1,
name2,
SUM(num1) num1,
SUM(num2) num2,
SUM(num3) num3
FROM t1, t2
WHERE t1.ID = t2.t1id
GROUP BY GROUPING SETS(name1,(name1, name2),());
----------------------------------------------------------------------------
NAME1 NAME2 NUM1 NUM2 NUM3
-----------------------------------------------
n1a n2a1 10 20 30
n2a2 20 30 40
小计 30 50 70
n1b n2b1 30 40 50
n2b2 40 50 60
n2b3 50 60 70
小计 120 150 180
合计 150 200 2508行被选择。各位帮看看,怎样才能把string1,string2也显示出来。
mber , num3 number, string1 varchar2(10), string2 varchar2(10));表已创建。SQL> insert into t2 values('a','n2a1',10,20,30,'strla1','str2a1');已创建 1 行。SQL> insert into t2 values('a','n2a2',20,30,40,'strla2','str2a2');已创建 1 行。SQL> insert into t2 values('b','n2b1',30,40,50,'strlb1','str2b1');已创建 1 行。SQL> insert into t2 values('b','n2b2',40,50,60,'strlb2','str2b2');已创建 1 行。SQL> insert into t2 values('b','n2b3',50,60,70,'strlb3','str2b3');已创建 1 行。SQL> commit ;提交完成。SQL> select nvl(t1.name,'总计'), a.name2 ,a.num1, a.num2, a.num3,b.string1, b.s
tring2 from
2 (select nvl(t1id,'total') t1id,
3 decode(grouping(name2),0,name2,decode(t1id,null,'','小计')) name2,
4 sum(num1) num1, sum(num2) num2, sum(num3) num3
5 from t2
6 group by rollup(t1id,name2 )) a
7 left join t1 on t1.id = a.t1id
8 left join t2 b on a.t1id = b.t1id and a.name2 = b.name2
9 order by nvl(t1.name,'总计'), a.name2 nulls last ;NVL(T1.NAM NAME2 NUM1 NUM2 NUM3 STRING1 STRING2
---------- ---------- ---------- ---------- ---------- ---------- ----------
nla n2a1 10 20 30 strla1 str2a1
nla n2a2 20 30 40 strla2 str2a2
nla 小计 30 50 70
nlb n2b1 30 40 50 strlb1 str2b1
nlb n2b2 40 50 60 strlb2 str2b2
nlb n2b3 50 60 70 strlb3 str2b3
nlb 小计 120 150 180
总计 150 200 250已选择8行。
t2.name2,sum(num1),sum(num2),sum(num3),max(string1),max(string2)
from t1,t2
where t1.id=t2.t1id
group by rollup(t1.name1,t2.name2)
select
decode(1,grouping(t1.name1),'合計',grouping(t2.name2),'小計',t1.name),
t2.name2,sum(num1),sum(num2),sum(num3),
decode(grouping(t1.name1)+grouping(t2.name2),0,max(string1)) string1,
decode(grouping(t1.name1)+grouping(t2.name2),0,max(string2))string2
from t1,t2
where t1.id=t2.t1id
group by rollup(t1.name1,t2.name2)