在MySQL中从多个表中检索记录

SELECT 
 log.timestamp AS log_timestamp, 
 u.user_id, 
 u.username AS user_name, 
 u.email AS user_email,
 role.name AS role_name,
 bal.amount AS balance_amount,
 log.message AS log_message
FROM users AS u 
JOIN users_role AS ur ON ur.user_id = u.user_id
JOIN users_roles AS role ON role.role_id = ur.role_id
JOIN users_log AS log ON u.user_id = log.user_id
LEFT JOIN users_balance AS bal ON bal.user_id = u.user_id
WHERE u.email = @UserEmail
  AND role.role_name = @RoleName
  AND log.status = @LogStatus

但根据数据的不同,您可能需要添加 GROUP BY 和一个 aggregate function 比如MAX或SUM,或者WHERE或 HAVING 条款。

你可以用 查询以从多个表中获取数据

请试试这个,

SELECT usr.*,usrlg.*,usrbl.*,usrrl.*,usrrls.* FROM users AS usr JOIN users_log AS usrlg ON(usrlg.user_id=usr.user_id) JOIN users_balance AS usrbl ON (usrbl.user_id=usr.user_id)
JOIN users_role AS usrrl ON(usrrl.user_id=usr.user_id) JOIN users_roles AS usrrls ON(usrrls.role_id=usrrl.role_id)
 WHERE usr.email='value' AND usrlg.status='value' AND usrrls.role_name='value'

你可以通过连接来实现。可以参考以下链接。

https://www.guru99.com/joins.html

http://www.mysqltutorial.org/mysql-join/

https://www.tutorialspoint.com/mysql/mysql-using-joins.htm