sql - 多个连接到同一个子查询
问题描述
我需要反复加入同一张桌子,但这看起来很难看。任何建议表示赞赏。 下面是简化的 SQL,我在子查询中有 8 个表,它会产生许多相同日期的重复记录,所以我只需要为每个客户端找到最新的记录。 (我认为 DB 和/或版本无关紧要,但我使用的是 DB2 11.1 LUW)
select c.client_num, a.eff_date, t.trx_date
from client c
join address a on(a.id = c.addr_id)
join transaction t on(t.addr_id = a.id)
{many other joins}
where {many conditions};
select SQ.*
from [ABOVE_SUBQUERY] SQ
join
(select client_num, max(eff_date) AS newest_date from [ABOVE_SUBQUERY] group by client_num) AA
ON(SQ.client_num = AA.client_num and SQ.eff_date = AA.newest_date)
join
(select client_num, max(trx_date) AS newest_date from [ABOVE_SUBQUERY] group by client_num) TT
ON(SQ.client_num = TT.client_num and SQ.trx_date = TT.newest_date)
解决方案
我只需要对每个客户的最新记录进行罚款。
你不能用row_number()
吗?
select t.*
from (select t.*,
row_number() over (partition by client_num order by eff_date desc, eff_time desc) as seqnum
from <whatever> t
) t
where seqnum = 1;