如何在SQL中联接多个表

本文介绍了如何在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

在此处

其他:

基于评论的变体此处