表结构
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-25
sql
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
请大家助忙！！！谢谢
SQL code:
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 i
