数据库为Oracle
数据库表大致是这样
Pid name birthday sal
1 A 20000101 1000
1 B 20010101 1000
1 A 20020101 1200
2 A 20000101 1000
2 A 20010101 1200
2 A 20020101 1200要求把pid为1的记录查出来,去除sal重复的记录,只显示日期大的那个,结果大致是这样(结果要能这样显示出来~)
Pid name birthday sal
1 A 20010101 1000
1 A 20020101 1200具体的oracle 语句怎么写~~~ 谢谢~
数据库表大致是这样
Pid name birthday sal
1 A 20000101 1000
1 B 20010101 1000
1 A 20020101 1200
2 A 20000101 1000
2 A 20010101 1200
2 A 20020101 1200要求把pid为1的记录查出来,去除sal重复的记录,只显示日期大的那个,结果大致是这样(结果要能这样显示出来~)
Pid name birthday sal
1 A 20010101 1000
1 A 20020101 1200具体的oracle 语句怎么写~~~ 谢谢~
group by pid ,sal
where pid=1
这条记录是写错了,还是要求这样?
根据你给得数据1 A 20000101 1000
1 B 20010101 1000
select 1 pid,'A' name,'20000101' birthday,1000 sal from dual
union all
select 1 pid,'B' name,'20010101' birthday,1000 sal from dual
union all
select 1 pid,'A' name,'20020101' birthday,1200 sal from dual
union all
select 2 pid,'A' name,'20000101' birthday,1000 sal from dual
union all
select 2 pid,'A' name,'20010101' birthday,1200 sal from dual
union all
select 2 pid,'A' name,'20020101' birthday,1200 sal from dual
)
select pid,max(name) name,max(birthday) birthday,sal from temp where pid = '1' group by pid,sal order by name结果
1 1 A 20020101 1200(日期大的那个)
2 1 B 20010101 1000
where t.pid = t2.pid and t.name = t2.name and t.sal = t2.sal and pid = 1
select pid,name,birthday,sal,
row_number()over(partition by sal order by birthday desc ) rn
from tt)
where rn =1
and pid=1
(select sal, min(birthday) birthday1 from t1 where pid = 1 group by sal) t2
where t1.pid = 1 and t1.birthday=t2.birthday1 and t2.sal=t2.sal试验如下:SQL> create table t1(pid int, name varchar2(10), birthday int, sal int);Table created.SQL> insert into t1 values(1, 'A', 20000101, 1000 );1 row created.SQL> insert into t1 values(1, 'B', 20010101, 1000 );1 row created.SQL> insert into t1 values(1, 'A', 20020101, 1200 );1 row created.SQL> insert into t1 values(2, 'A', 20000101, 1000 );1 row created.SQL> insert into t1 values(2, 'A', 20010101, 1200 );1 row created.SQL> insert into t1 values(2, 'A', 20020101, 1200 );1 row created.SQL> select * from t1; PID NAME BIRTHDAY SAL
---------- ---------- ---------- ----------
1 A 20000101 1000
1 B 20010101 1000
1 A 20020101 1200
2 A 20000101 1000
2 A 20010101 1200
2 A 20020101 12006 rows selected.SQL> select t1.pid,t1.name,t1.birthday,t1.sal from t1 ,
2 (select sal, min(birthday) birthday1 from t1 where pid = 1 group by sal) t2
3 where t1.pid = 1 and t1.birthday=t2.birthday1 and t2.sal=t2.sal; PID NAME BIRTHDAY SAL
---------- ---------- ---------- ----------
1 A 20000101 1000
1 A 20020101 1200
(
pid int,
name varchar2(10),
birthday varchar2(8),
sal int
)
insert into t_orcl values( 1 ,'A' , '20000101' ,1000 )
insert into t_orcl values(1 , 'B', '20010101' , 1000 );
insert into t_orcl values(1, 'A', '20020101', 1200);
insert into t_orcl values(2, 'A', '20000101', 1000);
insert into t_orcl values(2, 'A', '20010101', 1200);
insert into t_orcl values(2, 'A', '20020101', 1200);
--select * from t_orcl group by pid, sal;
select pid, name, birthday, sal
from (select pid,
name,
birthday,
sal,
rank() over(partition by pid, sal order by birthday desc) as cc
from t_orcl) t
where t.cc = 1
and t.pid = 1
--注意,请问问题的同学,建表的脚本给贴出来啊,这样会有更多人帮您的
from t_orcl a,
(select distinct(sal), max(birthday) as birthday from t_orcl group by sal) b
where a.sal(+)=b.sal and a.birthday(+)=b.birthday and pid=1
from tab
where pid=1
from tab
group by pid
where pid=1
where pid = 1
group by pid, sal
order by birthday
FROM (SELECT pid, NAME, birthday, sal,
ROW_NUMBER () OVER (PARTITION BY sal ORDER BY birthday DESC)
AS rownumber
FROM t_orcl
WHERE pid = 1)
WHERE rownumber = 1