如何从一张mysql表中得到不同的结果? [英] How to get different results from one mysql table?

问题描述

我在数据库中有一个用户表，其中存储了系统的所有用户.该表有一个 user_id、一个 business_name 和一个 first_name.一些用户是商人并获得企业名称，一些用户是消费者并获得名字.在第二个表中，我有一个 user_id 和一个 Merchant_id(定义交易)和一个金额的交易.两个 id 都引用用户表.

I have a user table in the database where all users of the system are stored. The table has a user_id and a business_name and a first_name. Some users are merchants and get a business name, some users are consumers and get a first name. In a second table I have transactions with a user_id and a merchant_id (which are defining the transaction) and an amount. Both ids reference to user table.

Table users:
user_id bus_name first_name role_id
1               Thomas        10
2      comp1                  7
3               Peter         10
4      comp2                  7
(role_id is defining with 10=consumer, 7=merchant)

Table transactions:
trans_id amount user_id merchant_id
1         12     1         2      
2         23     3         2 
3         34     3         4
4         19     1         4

现在我想将结果作为一个表进行查询:此表应包含交易金额、user_id、first_name、merchant_id 和 bus_name.

Now I want to have a query with a result as one table: This table should contain the transaction with amount, user_id, first_name, merchant_id and bus_name.

我想得到这个结果:

trans_id amount user_id first_name merchant_id bus_name
1          12     1        Thomas      2         comp1
2          23     3        Peter       2         comp1
3          34     3        Peter       4         comp2
4          19     1        Thomas      4         comp2

我遇到的问题是，要么我只得到 first_name 和空的 bus_name，要么我只得到 bus_name 而空的 first_name.我正在使用左连接:

I have the problem that either I get only the first_name and empty bus_name or I get only the bus_name but empty first_name. I am using a left join:

...
left join `users` 
on(
(`transactions`.`user_id` = `users`.`user_id`)
)
...

但为此，我会得到 user_id=1 的 first_name=Thomas 和 bus_name='' 将是空的，因为我只引用表中的一行，而不是 user_id=2 的不同用户.

But for this I would get for user_id=1 the first_name=Thomas and the bus_name='' would be empty because I only reference to one line in table and not also to different user with user_id=2.

但我想说的是:

for trans_id=1
get first_name FROM users WHERE transactions.user_id = users.user_id 
AND
get bus_name FROM users WHERE transactions.merchant_id = users.user_id

感谢您的帮助，我尝试了很多方法，但都不起作用.

Thanks for your help, I tried so many things but it does not work.

推荐答案

你必须两次加入用户表:

You have to join the user table twice:

SELECT t.*, u.first_name, m.bus_name
FROM transactions t
JOIN users as u
  ON t.user_id = u.user_id
JOIN users as m
  ON t.merchant_id = m.merchant_id

这篇关于如何从一张mysql表中得到不同的结果?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！