Table:
A B C D
1 11 wang 10
2 12 wang 20
2 13 wang 30
3 14 zhang 40
4 15 zhang 50
4 16 zhang 60要求:
1.统计出C列中不重复的数据和D列相应的和。
2.当A列重复时,只统计对应B列中的最大的D列数据。如下应统计数据为:
A B C D
1 11 wang 10
2 13 wang 30
3 14 zhang 40
4 16 zhang 60结果应为:
C D
wang 40
zhang 100哪们高手能指点迷津呀?
A B C D
1 11 wang 10
2 12 wang 20
2 13 wang 30
3 14 zhang 40
4 15 zhang 50
4 16 zhang 60要求:
1.统计出C列中不重复的数据和D列相应的和。
2.当A列重复时,只统计对应B列中的最大的D列数据。如下应统计数据为:
A B C D
1 11 wang 10
2 13 wang 30
3 14 zhang 40
4 16 zhang 60结果应为:
C D
wang 40
zhang 100哪们高手能指点迷津呀?
解决方案 »
- 安装oracle后第二天就不能启动了,ip地址变了。
- 请教个oracle的小问题
- ORACLE中一条sql语句同时修改两个表的数据
- 菜鸟级提问,请各位老大看看这条SQL为什么可以执行
- ORACLE8.1.6的managermentserver启动有问题?提示I/O重复操作,是怎么一回事情?
- ORacle9安装出错????ORacle9安装出错,显示TEMP目录下不够20MB,咋回事,我把TEMP目录下其他东西都清空啦,硬盘也足够
- 一条sql语句,以及sqlplus该如何用?急!急!急!!!!!!
- .net中Oracle数据库连接问题。新人请教。。。望指教
- 简单问题:怎样查找一个表在哪个表空间里?
- 已经配好了服务器,为什么客户端DBA Studio里没有数据库?
- ubuntu系统中运行java程序调用oracle 的sqlplus,有点挑战性
- oracle
select a,b,c,d
(select a,b,c,d,row_number()over(partition by a order by b desc) rn from table ) where rn=1;
with tmp as(
select a,b,c,d
(select a,b,c,d,row_number()over(partition by a order by b desc) rn from table ) where rn=1
)
select c,sum(d) from tmp group by c;
select * from table t where b=(select max(b) from table where a=t.a);select c, sum(d) from (
select c,d from table t where b=(select max(b) from table where a=t.a)
) t
group by c;
select c,sum(d) d
(select a,b,c,d,row_number() over(partition by a,c order by b desc) rn
from t
)tmp
where rn=1
group by c
;
select c,d from table t where b=(select max(b) from table where a=t.a)
) t
group by c;简单
SQL> edi
已写入 file afiedt.buf 1 with tb as
2 (select 1 A,11 B,'wang' C, 10 D from dual union all
3 select 2,12,'wang',20 from dual union all
4 select 2,13,'wang',30 from dual union all
5 select 3,14,'zhang',40 from dual union all
6 select 4,15,'zhang',50 from dual union all
7 select 4,16,'zhang',60 from dual)
8 select C,sum(D) d
9 from (select A,B,C,D,row_number() over(partition by A,C order by D desc) rn from tb) t
10 where t.rn=1
11* group by C
SQL> /C D
----- ----------
zhang 100
wang 40
from
(select a,b,c,d,
row_number() over(partition by a,c order by b desc) rowNum
from test
)t
where t.rowNum=1
group by c;
select * from tb a
where not exists(select 1 from tb b where a.a=b.a and a.b<b.b)
--2
select c,sum(d) as d from tb a
where not exists(select 1 from tb b where a.a=b.a and a.b<b.b)
group by c
--1
select * from tb a
where not exists(select 1 from tb b where a.a=b.a and a.c=b.c and a.b<b.b)
--2
select c,sum(d) as d from tb a
where not exists(select 1 from tb b where a.a=b.a and a.c=b.c and a.b<b.b)
group by c