SQL查询-小于或等于日期的联接 [英] SQL Query - join on less than or equal date
本文介绍了SQL查询-小于或等于日期的联接的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我在弄清楚如何格式化以下查询时遇到麻烦.我想取Table A并退出Table B.不仅在a.country = b.country上，而且在小于等于join_date的max start_date上.
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.
Postgres(Redshift)
表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
表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
最终结果
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
推荐答案
虽然不漂亮，但将条件直接放在JOIN中应该可以:
Not pretty, but putting your condition directly in the JOIN should work:
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
);
这篇关于SQL查询-小于或等于日期的联接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
