MYSQL连接两个具有相同字段值的表字段，但如果其他字段与另一个字段不匹配，则仍将包含该字段。

A lot of MYSQL Combining tables i have read today, but none of them fits with my problem. can anyone help me achive my desire result like in below?

我今天读过很多MYSQL合并表，但是没有一个适合我的问题。谁能帮我实现我的愿望结果如下?

TABLE 1:

表1:

|--------------------|
|        tbl1        |
|--------------------|
|user_id|points|year |
|--------------------|
| 1     | 3.2  | 2001|
| 1     | 2.2  | 2002|
| 1     | 3.8  | 2003|
| 1     | 3.6  | 2005|
| 2     | 1.2  | 2001|
| 2     | 1.2  | 2002|
| 2     | 1.2  | 2003|
|        *etc...     |
|--------------------|

TABLE 2:

表2:

|--------------------|
|        tbl2        |
|--------------------|
|user_id|amount|year |
|--------------------|
| 1     | 6.2  | 2001|
| 1     | 9.2  | 2002|
| 1     | 2.8  | 2003|
| 1     | 7.6  | 2004|
| 2     | 3.2  | 2001|
| 2     | 8.2  | 2002|
| 2     | 6.2  | 2003|
|        *etc...     |
|--------------------|

i only want to get the user_id1 i have the following query, i tried many query combination but didn't get any.

我只想获得user_id1我有以下查询，我尝试了很多查询组合，但没有得到任何查询。

SELECT
    `tbl1`.`points`,
    `tbl2`.`amount`,
    `tbl1`.`year`
FROM (
    SELECT *
    FROM `tbl1`
    WHERE `user_id` = 1
    ORDER BY `year` DESC
    ) AS `tbl1`
INNER JOIN (
    SELECT *
    FROM `tbl2`
    WHERE `user_id` = 1
    ORDER BY `year` DESC
) AS `tbl2` ON `tbl2`.`year` = `tbl1`.`year`

my problem to that query is that i use:

我对这个问题的问题是我使用:

     ON `tbl2`.`year` = `tbl1`.`year`

which will only return the match year. so yeah, i'm stuck.

它只会返回比赛年份。是的,我困了。

DESIRED RESULT:

预期的结果:

|----------------------------|
|     JOINED/COMBINED        |
|----------------------------|
|user_id|amount|points| year |
|----------------------------|
| 1     | 6.2  | 3.2  | 2001 |
| 1     | 9.2  | 2.2  | 2002 |
| 1     | 2.8  | 3.8  | 2003 |
| 1     | 7.6  | Null | 2004 |
| 1     | Null | 3.6  | 2005 |
|            *etc...         |
|----------------------------|

the problem in this case is that table2 has 2004 while table1 has 2005 both of them doesn't have another. but i still want to display them order by year.

这里的问题是表2有2004，而表1有2005这两个都没有。但我还是想按年显示它们的订单。

If it can't be done with query alone i will also accept PHP scripting to make this done but Query is my concern here more..

如果单凭查询无法完成，我也会接受PHP脚本来完成，但是查询是我更关心的问题。

2 个解决方案

#1

You are looking for a FULL OUTER JOIN = LEFT + RIGHT JOIN on MySQL:

在MySQL上寻找完整的外部连接=左+右连接:

SELECT *
FROM tbl1
LEFT JOIN tbl2 ON tbl1.user_id = tbl2.user_id
    AND tbl1.year = tbl2.year

UNION

SELECT *
FROM tbl1
RIGHT JOIN tbl2 ON tbl1.user_id = tbl2.user_id
    AND tbl1.year = tbl2.year

#2

The full answer will be:

完整的答案将是:

select user_id, sum(points) as poins, sum(amount) as amount, year from (
SELECT tbl1.user_id, tbl1.points, tbl2.amount, tbl1.year
FROM tbl1
  LEFT JOIN tbl2 ON tbl1.user_id = tbl2.user_id
                    AND tbl1.year = tbl2.year
UNION
SELECT tbl2.user_id,tbl1.points , tbl2.amount, tbl2.year
FROM tbl1
  RIGHT JOIN tbl2 ON tbl1.user_id = tbl2.user_id
                     AND tbl1.year = tbl2.year) tables
group by year
order by year

#1