select A.id, A.a1,A.a2,
C.c1,C.c2,
D.d1,
from
A inner join C on A.id=C.id
inner join D on A.id=D.id
inner join (SELECT DISTINCT NMAE, MAX (ID_NUMBER) over (partition by NMAE) ID_NUMBER FROM A) GG ON A.ID=GG.ID
select A.id, A.a1,A.a2,
C.c1,C.c2,
D.d1,
from
A inner join C on A.id=C.id
inner join D on A.id=D.id
inner join (SELECT ID,MAX(IDX) FROM B GROUP BY ID) GG ON A.ID=GG.ID
这个只是两个表,我把问题简化了,其实实际情况是很多表,前面已经用inner join 连接起来了,现在要把表B连接进去。
大概之前写的语句是这样的:
select A.id, A.a1,A.a2,
C.c1,C.c2,
D.d1,
from
A inner join C on A.id=C.id
inner join D on A.id=D.id
SELECT a.id,
(SELECT MAX (idx)
FROM b
WHERE a.id = b.id)
AS idx
FROM a
或者:
SELECT a.id, b.idx
FROM a, (SELECT id, MAX (idx) OVER (PARTITION BY id) FROM b) b
WHERE a.id = b.id
大概就这意思,不过两者性能谁更佳,可能还要看数据分布情况。