问题描述
朋友，我有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中联接多个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！