I'm having trouble figuring out how to format the following query. I want to take Table A and left join Table B. Not only on a.country = b.country but also on the max start_date that is less than equal to join_date.
我无法弄清楚如何格式化以下查询。我想取表A并离开连接表B.不仅在a.country = b.country上,而且在max start_date上小于等于join_date。
Postgres (Redshift)
Table A
ID join_date country email
----- ----------- --------- ------
124 '2013-10-03' US john@doe.com
423 '2013-04-21' CA bob@doe.com
412 '2013-03-30' US test@test.com
Table B
start_date country joined_from
------------- ----------- --------------
'2013-08-21' US google
'2014-01-02' CA yahoo
'2013-03-02' CA microsoft
'2013-02-10' US facebook
'2013-09-01' US yahoo
End Result
ID join_date country email start_date joined_from
------ ----------- --------- --------- ------------ -------------
124 '2013-10-03' US john@doe.com '2013-09-01' yahoo
423 '2013-04-21' CA bob@doe.com '2013-03-02' microsoft
412 '2013-03-30' US test@test.com '2013-02-10' facebook
1 个解决方案
#1
3
Not pretty, but putting your condition directly in the JOIN should work:
不漂亮,但将你的条件直接放在JOIN中应该有效:
SELECT a.ID, a.join_date, a.country, a.email, b.start_date, b.joined_from
FROM a LEFT JOIN b ON a.country = b.country
AND b.start_date = (
SELECT MAX(start_date) FROM b b2
WHERE b2.country = a.country AND b2.start_date <= a.join_date
);
#1
3
Not pretty, but putting your condition directly in the JOIN should work:
不漂亮,但将你的条件直接放在JOIN中应该有效:
SELECT a.ID, a.join_date, a.country, a.email, b.start_date, b.joined_from
FROM a LEFT JOIN b ON a.country = b.country
AND b.start_date = (
SELECT MAX(start_date) FROM b b2
WHERE b2.country = a.country AND b2.start_date <= a.join_date
);