现在有表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
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
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 16SQL> 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 14WITH语句更加清晰
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
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
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
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