表结构
create table COSTUMER
(
ID VARCHAR2(9), //id号
NAME VARCHAR2(18), SEX CHAR(1),
SALARY NUMBER(6,2), //工资
BIRTHDAY DATE //日期
)
ID NAME SEX SALARY BIRTHDAY
2 王雪梅 0 2000 2010-3-1
1 刘祥 1 1000 2010-3-10
1 刘祥 1 1500 2010-3-1
3 0 2010-3-16
1 刘祥 1200 2010-3-25sql
select c.id,count(c.id),max(c.birthday)from costumer c group by c.id;
想得到如下结果:
既(每个id对应最大日期,和最大日期对应的工资,还有对就每个id的记录总条数)ID COUNT(C.ID) MAX(C.BIRTHDAY) 工资
2 1 2010-3-1 2000
1 3 2010-3-25 1200
3 1 2010-3-16请大家助忙!!!谢谢
create table COSTUMER
(
ID VARCHAR2(9), //id号
NAME VARCHAR2(18), SEX CHAR(1),
SALARY NUMBER(6,2), //工资
BIRTHDAY DATE //日期
)
ID NAME SEX SALARY BIRTHDAY
2 王雪梅 0 2000 2010-3-1
1 刘祥 1 1000 2010-3-10
1 刘祥 1 1500 2010-3-1
3 0 2010-3-16
1 刘祥 1200 2010-3-25sql
select c.id,count(c.id),max(c.birthday)from costumer c group by c.id;
想得到如下结果:
既(每个id对应最大日期,和最大日期对应的工资,还有对就每个id的记录总条数)ID COUNT(C.ID) MAX(C.BIRTHDAY) 工资
2 1 2010-3-1 2000
1 3 2010-3-25 1200
3 1 2010-3-16请大家助忙!!!谢谢
解决方案 »
- Oracle10g exp导出某个用户下的所有对象和数据该怎么写?
- Oracle日志如何查看
- oracle客户端连接时报ora-12560 tns 协议适配器错误 重启后正常 急!跪求!
- 急!
- 使用oracle net configuration assistant工具 配置本地net服务名错误
- 高分求在PL/SQL中读取文件的例子(急!急!急!)
- 如何用oracle存贮空间数据
- [急]PL/SQL写查询条件
- 表空间使用的问题,大家帮忙看看是什么问题
- 急,小弟求助项目中一个问题:Oracle怎么自动把某一dbf文件中数据导入?(见内)
- 在pl/sql 中创建用户
- ALL_OBJECTS权限问题
with COSTUMER as
(
select 2 ID ,'王雪梅' name, 0 sex, 2000 salary,to_date('2010-3-1','yyyy-MM-dd') birthday from dual union all
select 1 ID ,'刘祥' name, 1 sex, 1000 salary,to_date('2010-3-10','yyyy-MM-dd') birthday from dual union all
select 1 ID ,'刘祥' name, 1 sex, 1500 salary,to_date('2010-3-1','yyyy-MM-dd') birthday from dual union all
select 3 ID ,'萨撒' name,1 sex, 1200 salary,to_date('2010-3-16','yyyy-MM-dd') birthday from dual union all
select 1 ID ,'刘祥' name,0 sex, 1100 salary,to_date('2010-3-25','yyyy-MM-dd') birthday from dual
)
select id, cnt, birthday, salary
from (select id,
name,
sex,
salary,
birthday,
rank() over(partition by id order by birthday desc) rk,
count(*) over(partition by id order by 1) cnt
from COSTUMER) t
where t.rk = 1
SQL> with COSTUMER as
2 (
3 select 2 ID ,'王雪梅' name, 0 sex, 2000 salary,to_date('2010-3-1','yyyy-MM-dd') birthday from dual union all
4 select 1 ID ,'刘祥' name, 1 sex, 1000 salary,to_date('2010-3-10','yyyy-MM-dd') birthday from dual union all
5 select 1 ID ,'刘祥' name, 1 sex, 1500 salary,to_date('2010-3-1','yyyy-MM-dd') birthday from dual union all
6 select 3 ID ,'萨撒' name,1 sex, 1200 salary,to_date('2010-3-16','yyyy-MM-dd') birthday from dual union all
7 select 1 ID ,'刘祥' name,0 sex, 1100 salary,to_date('2010-3-25','yyyy-MM-dd') birthday from dual
8 )
9 select id, cnt, birthday, salary
10 from (select id,
11 name,
12 sex,
13 salary,
14 birthday,
15 rank() over(partition by id order by birthday desc) rk,
16 count(*) over(partition by id order by 1) cnt
17 from COSTUMER) t
18 where t.rk = 1
19 / ID CNT BIRTHDAY SALARY
---------- ---------- ----------- ----------
1 3 2010-3-25 1100
2 1 2010-3-1 2000
3 1 2010-3-16 1200SQL
select sub.*,c.salary
from (select c.id,count(c.id) ci ,max(c.birthday) bd from costumer c group by c.id) sub,
costumer c
where c.id = sub.id and C.BIRTHDAY = SUB.bd
order by sub.id
select '2' id,'王雪梅' name,'0' sex,2000 salary,date'2010-3-1' birthday from dual
union all select '1','刘祥','1',1000,date'2010-3-10' from dual
union all select '1','刘翔','1',1500,date'2010-3-1' from dual
union all select '3','','0',null,date'2010-3-16' from dual
union all select '1','刘翔','1',1200,date'2010-3-25' from dual)
select id,count(1),max(birthday),max(salary)keep(dense_rank last order by birthday)salary
from costumer
group by id
这个日期应该指工资变动日期吧,怎么是BIRTHDAY呢
COSTUMER 是customer吧....