数据库查询排序取第一个或空的问题

[vip__888]

有两张表
A表结构

Id(主键),ID1,ID2 字段

B表结构

Id(A表主键)，time1，ID3字段

现在是要查询A表所有数据 和 B表的 ID3，B表为A表的外键表，有多条或没有记录，要求按照time1 倒叙取最新的一条ID3，没有就置空。


sqlserver的 子查询在这里用不了order by，不熟悉 oracle，求语句支援。

[vip__888]

顶一下

[卖水果的net]

SQL> 
SQL> create table A(id int primary key, name varchar(10));
Table created
SQL> create table B(id int, time1 date, id3 int);
Table created
SQL> alter table B add constraint fk_a_b foreign key (id3) references a(id);
Table altered
SQL> begin
  2      insert into A values(100, 'xxx');
  3      insert into A values(200, 'yyy');
  4      insert into A values(300, 'zzz');
  5      insert into B values(1,sysdate() - 1, 100);
  6      insert into B values(2,sysdate() - 0, 100);
  7      insert into B values(3,sysdate() + 1, 100);
  8      insert into B values(4,sysdate() - 1, 200);
  9      insert into B values(5,sysdate() - 0, 200);
 10  end;
 11  /
PL/SQL procedure successfully completed
SQL> col id format 99999;
SQL> col id3 format 99999;
SQL> col name format 10;
SQL> with bx as(
  2    select B.id,
  3           B.time1,
  4           B.id3,
  5           row_number() over(partition by id3 order by time1 desc) rn
  6      from B
  7  )
  8  select A.id, A.name, bx.id, bx.time1, bx.id3
  9    from A
 10    left join BX on A.id = BX.id3 and BX.rn = 1;
   ID NAME          ID TIME1         ID3
----- ---------- ----- ----------- -----
  100 xxx            3 2018/10/27    100
  200 yyy            5 2018/10/26    200
  300 zzz                          
SQL> drop table B purge;
Table dropped
SQL> drop table A purge;
Table dropped

SQL> 

[碧水幽幽泉]

select a.id,a.id1,a.id2,b.id3 from a left join (select id,id3,row_number()over(partition by id order by time1 desc) as rn from b) b on b.id = a.id and b.rn = 1

[vip__888]

引用 3 楼 qq646748739 的回复:

select a.id,a.id1,a.id2,b.id3 from a left join (select id,id3,row_number()over(partition by id order by time1 desc) as rn from b) b on b.id = a.id and b.rn = 1

我在B表还有ID4字段 这个怎么写呢？

[vip__888]

引用 2 楼 wmxcn2000 的回复:

SQL> 
SQL> create table A(id int primary key, name varchar(10));
Table created
SQL> create table B(id int, time1 date, id3 int);
Table created
SQL> alter table B add constraint fk_a_b foreign key (id3) references a(id);
Table altered
SQL> begin
  2      insert into A values(100, 'xxx');
  3      insert into A values(200, 'yyy');
  4      insert into A values(300, 'zzz');
  5      insert into B values(1,sysdate() - 1, 100);
  6      insert into B values(2,sysdate() - 0, 100);
  7      insert into B values(3,sysdate() + 1, 100);
  8      insert into B values(4,sysdate() - 1, 200);
  9      insert into B values(5,sysdate() - 0, 200);
 10  end;
 11  /
PL/SQL procedure successfully completed
SQL> col id format 99999;
SQL> col id3 format 99999;
SQL> col name format 10;
SQL> with bx as(
  2    select B.id,
  3           B.time1,
  4           B.id3,
  5           row_number() over(partition by id3 order by time1 desc) rn
  6      from B
  7  )
  8  select A.id, A.name, bx.id, bx.time1, bx.id3
  9    from A
 10    left join BX on A.id = BX.id3 and BX.rn = 1;
   ID NAME          ID TIME1         ID3
----- ---------- ----- ----------- -----
  100 xxx            3 2018/10/27    100
  200 yyy            5 2018/10/26    200
  300 zzz                          
SQL> drop table B purge;
Table dropped
SQL> drop table A purge;
Table dropped

SQL> 

我在B表还有ID4字段 这个怎么写呢？ 取ID3的时候要取ID4在一条记录里面

[碧水幽幽泉]

select a.id,a.id1,a.id2,b.id3,b.id4 from a left join (select id,id3,id4,row_number()over(partition by id order by time1 desc) as rn from b) b on b.id = a.id and b.rn = 1

[vip__888]

谢谢大家的回复，搞定了

[m0_50842294]

大佬 带带弟弟