表结构
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请大家助忙!!!谢谢
解决方案 »
- 在同一台机器装上了 oracle服务器 和 客户端
- 如何得到oracle指定表空间的所有表名
- oracle 性能比较,谁快谁慢?
- oracle update 更新多字段用一条sql 的预设默认值的问题
- 錯誤訊息詢問
- 请教一个oracle使用显示游标问题?
- 我使用JAVA JDBC的批量提交插入数据遇到异常(一条违反约束的信息),其后的插入无法进行
- 序列和max函数产生主键值,哪个更好,各有什么优缺点?
- oracle存储过程如何建立?
- 怎么用Enterprise Manager Configuration Assistant配置一个资料档案库呀?
- 在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吧....