PostgreSQL联接2个表

本文介绍了PostgreSQL联接2个表的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我有2个表tab1和tab2，tab2(tab1_id)引用了tab1(id)

i have 2 tables tab1 and tab2, tab2(tab1_id) references tab1(id)

tab2的tab1(id)值不同

tab2 has different values for the tab1(id)

我需要一个连接，该连接将tab1与tab2中的操作列连接在一起， 以及ID的最新值.

i need a join which will join tab1 with action column from tab2, and latest value for the id.

tab1 :- id | user_file_id | created_date | modified_date ----+--------------+---------------------+--------------------- 2 | 102 | 2012-01-12 01:23:46 | 2012-03-04 16:52:28 4 | 104 | 2012-01-12 15:45:10 | 2012-01-15 02:23:40 6 | 106 | 2012-01-18 00:14:34 | 2012-01-24 20:17:49 7 | 107 | 2012-02-02 01:07:14 | 2012-04-17 09:29:17 8 | 108 | 2012-02-15 13:16:24 | 2012-03-26 10:30:51 9 | 109 | 2012-02-20 18:08:48 | 2012-04-09 06:14:58 10 | 110 | 2012-02-24 20:49:10 | 2012-03-23 11:36:41 11 | 111 | 2012-03-05 22:38:14 | 2012-03-16 04:29:35 (8 rows) tab2:- id | action | tab1_id ----+--------+--------- 1 | 1 | 2 3 | 2 | 2 4 | 1 | 2 5 | 2 | 2 6 | 1 | 2 7 | 3 | 2 2 | 1 | 4 8 | 1 | 6 9 | 1 | 7 10 | 1 | 8 11 | 1 | 9 12 | 1 | 10 13 | 1 | 11 (13 rows)

tab1和tab2都连接在一起，得到的输出为:-

the both tab1 and tab2 joined to get the output as :-

id | user_file_id | created_date | modified_date | action ----+--------------+---------------------+---------------------+-------- 2 | 102 | 2012-01-12 01:23:46 | 2012-03-04 16:52:28 | 3 4 | 104 | 2012-01-12 15:45:10 | 2012-01-15 02:23:40 | 1 6 | 106 | 2012-01-18 00:14:34 | 2012-01-24 20:17:49 | 1 7 | 107 | 2012-02-02 01:07:14 | 2012-04-17 09:29:17 | 1 8 | 108 | 2012-02-15 13:16:24 | 2012-03-26 10:30:51 | 1 9 | 109 | 2012-02-20 18:08:48 | 2012-04-09 06:14:58 | 1 10 | 110 | 2012-02-24 20:49:10 | 2012-03-23 11:36:41 | 1 11 | 111 | 2012-03-05 22:38:14 | 2012-03-16 04:29:35 | 1 (8 rows)

推荐答案

尝试:

select t1.*, t2.action from tab1 t1 join (select t.*, row_number() over (partition by tab1_id order by id desc) rn from tab2 t) t2 on t1.id = t2.tab1_id and t2.rn = 1

如果要允许tab1上的行没有在tab2上记录任何动作，请将联接更改为左联接.

Change the join to a left join if you want to allow for a row on tab1 having no actions recorded on tab2.