我想连接两个表,其模式如下所示...!!
Table :
team_posts
id
team_id
text
location_name
stars_count
comments_count
created_at
updated_at
team_feed_activites
id
team_id
operation
created_at
updated_at
表值为
team_posts
id | team_id | text | location_name | stars_count | comments_count | created_at | updated_at
1 | 23 | crow | india | 1 | 2 | 2016-03-18 06:30:00 | 2016-03-18 06:30:00
2 | 24 | bird | india | 1 | 2 | 2016-03-18 06:30:00 | 2016-03-19 06:30:00
3 | 25 | span | india | 33 | 2 | 2016-03-18 06:30:00 | 2016-03-18 06:30:00
4 | 25 | bang | india | 3 | 2 | 2016-03-18 06:30:00 | 2016-03-12 06:30:00
5 | 27 | crow | india | 1 | 2 | 2016-03-18 06:30:00 | 2016-03-18 06:30:00
6 | 23 | crow | india | 1 | 2 | 2016-03-18 06:30:00 | 2016-01-18 06:30:00
7 | 23 | crow | india | 7 | 2 | 2016-03-18 06:30:00 | 2016-03-18 06:30:00
8 | 23 | hen | india | 1 | 2 | 2016-03-18 06:30:00 | 2016-03-01 06:30:00
9 | 23 | mani | india | 1 | 2 | 2016-03-18 06:30:00 | 2016-03-18 06:30:00
10 | 23 | dog | india | 1 | 2 | 2016-03-18 06:30:00 | 2016-03-18 06:30:00
11 | 29 | crow | india | 10 | 2 | 2016-03-18 06:30:00 | 2016-02-26 06:30:00
12 | 29 | god | india | 1 | 2 | 2016-03-18 06:30:00 | 2016-03-18 06:30:00
13 | 29 | pen | india | 44 | 2 | 2016-03-18 06:30:00 | 2016-03-25 06:30:00
team_feed_activites
id | team_id | operation | created_at | updated_at
1 | 10 | established | 2016-03-18 06:30:00 | 2016-03-18 06:30:00
2 | 23 | established | 2016-03-19 06:30:00 | 2016-03-19 06:30:00
3 | 23 | modified | 2016-03-19 06:30:00 | 2016-03-19 06:30:00
4 | 24 | captian changed | 2016-03-19 06:30:00 | 2016-03-19 06:30:00
5 | 23 | captian added | 2016-03-19 06:30:00 | 2016-03-19 06:30:00
6 | 27 | won | 2016-03-19 06:30:00 | 2016-03-19 06:30:00
7 | 23 | won | 2016-03-19 06:30:00 | 2016-03-19 06:30:00
8 | 23 | paricipated | 2016-03-19 06:30:00 | 2016-03-19 06:30:00
9 | 23 | lost | 2016-03-19 06:30:00 | 2016-03-19 06:30:00
10 | 23 | changed pic | 2016-03-19 06:30:00 | 2016-03-19 06:30:00
11 | 23 | addded image | 2016-03-19 06:30:00 | 2016-03-19 06:30:00
12 | 30 | established | 2016-03-19 06:30:00 | 2016-03-19 06:30:00
现在我需要的是创建一个视图,其中该表根据日期合并到表中updated_at。这样我就可以每天看到团队的进展,以便我可以将其用作团队提要。我可以有人帮我怎么做..!!提前感谢..!!
只需使用简单的INNER JOIN
即可根据updated_at
字段合并两个表中的数据
SELECT * FROM team_posts tp
INNER JOIN team_feed_activites tfa ON tp.updated_at = tfa.updated_at;
您可以使用此查询 SELECT tp.field,tfa.field FROM team_posts tp,team_feed_activites tfa
但是请谨慎使用,这将给重复记录一些时间并为每个字段分配新名称,因为字段中有相同的名称,因此它将替换它
字段名称分配:tp.id as tp_id
使用
LEFT JOIN
和 RIGHT JOIN
连接,然后使用 UNION ALL
加入他们 你可以试试这个。
SELECT *
FROM
(SELECT a.* FROM team_posts a
LEFT JOIN
team_feed_activities b
ON a.updated_at=b.updated_at
UNION ALL
SELECT a.* FROM team_posts a
RIGHT JOIN
team_feed_activities b
ON a.updated_at=b.updated_at) subquery
ORDER BY updated_at DESC
注意:两个(子查询)SELECT
列必须具有相同数量的列。例:
SELECT a.*, b.operation
其次,它必须是相同的
SELECT a.*, b.operation
或SELECT a.*, b.team_id
只要两个表显示相同数量的列。
我尝试了下面的代码,并在接受时得到了输出。
create view team_feed AS
select tp.id as post_id,
tp.team_id as post_team_id,
tp.text,
tp.location_name,
tp.stars_count,
tp.comments_count,
tp.created_at as post_created_at,
tp.updated_at as post_updated_at,
'' as team_feed_activites_id,
'' as team_feed_activites_team_id,
'' as team_feed_activites_operation,
'' as team_feed_activites_created_at,
'' as team_feed_activites_updated_at,
tp.updated_at as event_updated_at
from team_posts as tp
UNION
select '' as post_id,
'' as post_team_id,
'' as text,
'' as location_name,
'' as stars_count,
'' as comments_count,
'' as post_created_at,
'' as post_updated_at,
tfa.id as team_feed_activites_id,
tfa.team_id as team_feed_activites_team_id,
tfa.operation as team_feed_activites_operation,
tfa.created_at as team_feed_activites_created_at,
tfa.updated_at as team_feed_activites_updated_at,
tfa.updated_at as event_updated_at
from team_feed_activites as tfa
在上面的查询中,我根据表选择将唯一列作为event_updated_at,并将其作为视图。为了从中获取数据,我使用了下面的查询。
select post_id,
post_team_id,
text,
location_name,
stars_count,
comments_count,
team_feed_activites_id,
team_feed_activites_team_id,
team_feed_activites_operation
from team_feeds
order by event_updated_at ASC