我有一个没有日期的表,并希望加入带有日期的表。我正在对 id 和 bn_number 进行左连接。id可以有多个日期,我显然想要其他表中的最新日期,因为它每个id都有多个日期。 我不确定如何至少获取所有日期,然后我可以选择最新的日期。
select Reg_Property_id,a.Bnd_nbr,account_balance,abs(account_balance) as Bond_Balance,a.Bnd_regDate
into #Jan2014ValidFin
from #Jan2014Valid aa
left join Pr_analytics..bond a
on aa.Reg_Property_id=a.Prop_id
and aa.bnd_nbr=a.Bnd_nbr
where aa.reg_property_id is not null
.SQL
请协助。
使用 ROW_NUMBER(( 窗口函数获取最近的日期:
SELECT c.*
FROM (
SELECT a.cols, b.cols, ROW_NUMBER() OVER (PARTITION BY b.colID1,b.colID2 ORDER BY b.theDate DESC) AS rn
FROM a
LEFT OUTER JOIN b ON a.col1 = b.col1
AND a.col2 = b.col2
) c
WHERE c.rn = 1
一个简单的group by
应该可以解决问题:
SELECT
Reg_Property_id -- What table is this from?
,a.Bnd_nbr
,account_balance -- What table is this from?
,abs(account_balance) as Bond_Balance -- What table is this from?
,max(a.Bnd_regDate) as Bnd_regDate
into #Jan2014ValidFin
from #Jan2014Valid aa
left join Pr_analytics..bond a
on aa.Reg_Property_id = a.Prop_id
and aa.bnd_nbr = a.Bnd_nbr
where aa.reg_property_id is not null
group by
Reg_Property_id
,a.Bnd_nbr
,account_balance
,abs(account_balance)
请注意,如果没有日期 (a.Bnd_regDate(,您将获得 NULL
另请注意,如果在 #Jan2014Valid 中找到任何标有"这是来自哪个表"的值,则需要聚合它们(最大值、总和等(或将它们包含在分组依据子句中 - 我无法从提供的信息中判断是哪个。