如何在SQL中联接多个表 [英] How to join multiple tables in sql

问题描述

朋友，我有3张表充值，撤回并转移这3张表属于user表.我必须找到属于用户的所有记录.我尝试使用内部联接，如下所示:-

Hi friends I have 3 table topups, withdraws and transfers these 3 tables belongs to the user table. I have to find all the records which belongs to the users. I tried with inner join as follows:-

SELECT * FROM users u
INNER JOIN topups t
  ON u.id = t.user_id
INNER JOIN withdraws w
  ON u.id = w.user_id
INNER JOIN transfers tf
  ON u.id = tf.user_id

但是此查询仅返回3个表之间的公共记录.我必须为每个表查找属于用户的所有那些记录.

But this query returns only the common records between the 3 tables. i have to find all those records which belongs to the user for each table.

假设我在充值中有2条记录属于用户ID 1，在提款中有3条记录属于用户ID 2，在转移中有5条记录属于用户ID 3，所以我应该获得总计10条记录.

Suppose i have 2 records in topups which belongs to user id 1, 3 records in withdraws which belongs to user id 2 and 5 records in transfers which belongs to user id 3 so i should get the total 10 records.

样本数据:-

充值

+--------+---------+---------+
| amount | result  | user_id |
+--------+---------+---------+
|     10 | success |       1 |
|     20 | failed  |       2 |
+--------+---------+---------+

提现

+---------+----------+
|w_amount |  user_id |
+---------+----------+
|     10  |        1 |
|     20  |        2 |
|     30  |       10 |
+---------+----------+

转移

+--------+--------+---------+
| method | amount | user_id |
+--------+--------+---------+
| abc    |     10 |       3 |
| xyz    |     20 |       4 |
+--------+--------+---------+

用户

+----+---------+--------+
| id |  f_name | l_name |
+----+---------+--------+
|  1 |    abc  |    xyz |
|  2 |    abc  |    xyz |
|  3 |    abc  |    xyz |
|  4 |    abc  |    xyz |
|  5 |    abc  |    xyz |
|  6 |    abc  |    xyz |
+----+---------+--------+

预期输出

+--------+---------+---------+----------+---------+
| amount | result  | user_id | w_amount |  method |
+--------+---------+---------+----------+---------+
|     10 | success |       1 |          |         |
|     20 | failed  |       2 |          |         |
|        |         |       1 |       10 |         |
|        |         |       2 |       20 |         |
|        |         |       3 |          | abc     |
|        |         |       4 |          | xyz     |
+--------+---------+---------+----------+---------+

请帮助，谢谢.

推荐答案

让他们与用户和数字保持联系.

Left joining them to the users and to a number works for this.

SELECT 
 tup.amount, tup.result, 
 usr.id as user_id, 
 wd.w_amount, 
 trans.method
FROM users usr
CROSS JOIN (SELECT generate_series n FROM generate_series(1, 3)) AS nr
LEFT JOIN topups tup ON tup.user_id = usr.id AND nr.n = 1
LEFT JOIN withdraws wd ON wd.user_id = usr.id AND nr.n = 2
LEFT JOIN transfers trans ON trans.user_id = usr.id AND nr.n = 3
WHERE (tup.user_id IS NOT NULL OR wd.user_id IS NOT NULL OR trans.user_id IS NOT NULL)
ORDER BY tup.user_id, wd.user_id, trans.user_id

在此处

其他:

基于评论的变体此处

这篇关于如何在SQL中联接多个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！