关于分组合计的问题
现在有表A其中有4个字段 x1(CHAR) x2(NUMBER) x3(CHAR) note(CHAR)
现在的数据为：
x1 x2 x3 note
-----------------------------
00001 40 00003 备注12
00002 50 00002 备注13
00003 60 00004 备注14
00001 70 00002 备注15
00002 30 00032 备注16
现在要求输出内容为
x1 sum(x2) note
其中note为x1里面x3数值最小的纪录。
比如上边数据应该输出
x1 sum(x2) note
------------------------
00001 110 备注15
00002 80 备注13
00003 60 备注14
[解决办法]
select t2.x1,t2.sumX2 ,t1.note
from
(
SELECT x1, note
FROM a
WHERE x2 IN (SELECT MIN (x2)
FROM a)
) t1
(
SELECT x1, SUM (x2) sumX2
FROM a
GROUP BY x1) t2
where t1.x1 = t2.x1
[解决办法]
--测试数据
create table a (x1 CHAR(10),x2 NUMBER, x3 CHAR(10), note CHAR(10));
insert into a
select '00001 ',40, '00003 ', '备注12 ' from dual union all
select '00002 ',50, '00002 ', '备注13 ' from dual union all
select '00003 ',60, '00004 ', '备注14 ' from dual union all
select '00001 ',70, '00002 ', '备注15 ' from dual union all
select '00002 ',30, '00032 ', '备注16 ' from dual;
--执行查询
select a1.x1,b.s,a1.note from
(select x1,note from a where x3 in (select min(x3) from a group by x1)) a1,
(select x1,sum(x2) s from a group by x1) b where a1.x1=b.x1
--查询结果
00001 110备注15
00002 80备注13
00003 60备注14
[解决办法]
我的测试
SQL> select * from b;
X1 X2 X3 NOTE
----- ---------- ----- --------------------
00001 40 00003 note 12
00002 50 00002 note 13
00003 60 00004 note 14
00001 70 00004 note 15
00002 30 00032 note 16
SQL> WITH t_temp AS (SELECT x1,SUM(x2) as x2 FROM b GROUP BY x1),
t_temp1 AS (SELECT x1,min(x3) as x3 FROM b GROUP BY x1)
SELECT t_temp.x1,
t_temp.x2,
t.note t
FROM t_temp,t_temp1,b t
WHERE t_temp.x1 = t_temp1.x1 AND t_temp.x1 = t.x1 AND t_temp1.x3 = t.x3
/ 2 3 4 5 6 7 8
X1 X2 T
----- ---------- --------------------
00001 110 note 12
00002 80 note 13
00003 60 note 14
WITH语句更加清晰
[解决办法]
我测试是成功的，试试看~~
create table aaaaa(
x1 varchar2(6),
x2 number,
x3 varchar2(6),
note varchar2(10)
);
insert into aaaaa values( '00001 ',40, '00003 ', '备注12 ');
insert into aaaaa values( '00002 ',50, '00002 ', '备注13 ');
insert into aaaaa values( '00003 ',60, '00004 ', '备注14 ');
insert into aaaaa values( '00001 ',70, '00002 ', '备注15 ');
insert into aaaaa values( '00002 ',30, '00032 ', '备注16 ');
commit;
select * from aaaaa;
select a.x1,sum(x2) over(partition by a.x1 order by a.x1) sum(x2),a.note note
from aaaaa a,
(
select aa.x1,min(aa.x3) x3
from aaaaa aa
group by aa.x1
)tt
where a.x1 = tt.x1
and a.x3 = tt.x3;
x1 sum(x2) note
------------------------
00001 110 备注15
00002 80 备注13
00003 60 备注14
[解决办法]
不好意思，小改一下 !!~~~
create table aaaaa(
x1 varchar2(6),
x2 number,
x3 varchar2(6),
note varchar2(10)
);
insert into aaaaa values( '00001 ',40, '00003 ', '备注12 ');
insert into aaaaa values( '00002 ',50, '00002 ', '备注13 ');
insert into aaaaa values( '00003 ',60, '00004 ', '备注14 ');
insert into aaaaa values( '00001 ',70, '00002 ', '备注15 ');
insert into aaaaa values( '00002 ',30, '00032 ', '备注16 ');
commit;
select * from aaaaa;
select a.x1,sum(x2) over(partition by a.x1 order by a.x1) sum_x2,a.note note
from aaaaa a,
(
select aa.x1,min(aa.x3) x3
from aaaaa aa
group by aa.x1
)tt
where a.x1 = tt.x1
and a.x3 = tt.x3;
x1 sum_x2 note
------------------------
00001 110 备注15
00002 80 备注13
00003 60 备注14
[解决办法]
又想了一种，用分析函数
SQL> select a.x1,yy.sum_x2,a.note note
from aaaaa a,
(
select aa.x1,aa.x3,aa.note,row_number() over(partition by aa.x1 order by aa.x3 asc) rn
from aaaaa aa
)tt,
(
select distinct i.x1,sum(i.x2) over(partition by i.x1 order by i.x1) sum_x2
from aaaaa i
)yy
where rn = 1
and a.x1 = yy.x1
and a.note = tt.note;
X1 SUM_X2 NOTE
------ ---------- ----------
00002 80 备注13
00003 60 备注14
00001 110 备注15
[解决办法]
方法真多，我也试试
select t.x1,t.x2,v.note
from
(
select x1,sum(x2) x2,max(x2) max_x2
from table
group by x1
) t,table v
where t.x1=v.x1 and t.max_x2=v.x2
[解决办法]
jiazheng(飛天) 似乎理解错题意了，下面这个即简单又容易理解且和数据无关
select
t.x1 , sum(t.x2) ,
(select a.note from atest a
where a.x1 = t.x1
and a.x3 = (select min(b.x3) from
atest b where b.x1 = t.x1
) and rownum = 1 )
from atest t
group by t.x1
