如何链接和显示来自两个不同表的 MySQL 数据?

Question

我有两个表，分别名为"stats"和"users">

用户表包含所有典型的用户数据，如ID，用户名，密码，电子邮件(列(统计表具有ID，攻击，防御，OSSTATS，黄金，食物(列(

我想并排显示这两个表中的数据，并通过其 IDS 链接数据 例如

Rank   user_uid   ostats     attack    defense    gold 
1    Test        10          5         5        100
2    Test2       8           2         6        60
3    Test3       6           5         1        40

用户名来自表"用户"，其余来自表"统计"

所以首先我想知道如何链接和显示来自同一ID的数据，如用户名(user_id=1(和ostats，攻击，防御，黄金，食物(id=1(

然后我希望它们按它们的"ostats"排序(我还没有在任何表中有名为"rank"的列，只是不知道如何使用整体统计数据创建排名(

Answer 1

你可以做类似的事情(未经测试(

SELECT u.username, s.overall, s.attack, s.defense, s.gold 
FROM stats s JOIN users u on s.user_uid = u.id 
ORDER BY s.overall;

排名的可能解决方案：

set @row_number=0;
SELECT (@row_number:=@row_number+1) as rank, u.username, s.overall, s.attack, s.defense, s.gold 
FROM stats s JOIN users u on s.user_uid = u.id 
ORDER BY s.overall;

另一个看起来很可怕的尝试：

set @row_number = (select count(*) from users) + 1;
select (@row_number:=@row_number-1) as rank,  u.username, s.overall from 
stats s join users u on s.user_uid = u.id order by s.overall desc;
set @row_number = 0;

在PHP代码中，您必须将其作为两个查询运行以设置变量，然后运行实际的排名查询。这样，在运行此命令时，rank 变量始终设置为 0。请注意，我使用了不同的表名和列名，只是为了简化一些事情。请记住根据您的特定需求进行调整。

// connect to database
$conn = mysqli_connect("localhost", "user", "password", "database");
// this query will set a variable to 0.
$setSql = "SET @row_number = 0;";
// run the query. This will return a boolean - true or false, depending on whether or not the query ran successfully
$variableSet = mysqli_query($conn, $setSql);
// if the query ran successfully
if($variableSet){
// setup the actual ranking query
$statsSql = "select 
(@row_number:=@row_number+1) as rank,
u.id,
u.username,
s.overall
from
mstats s 
join 
musers u
on 
s.muser = u.id
order by 
s.overall desc;";
$ranks = mysqli_query($conn, $statsSql);
if(!$ranks){
// dump error from rank query
var_dump($conn->error); 
} else {
// dump results as associative array
var_dump($ranks->fetch_all(MYSQLI_ASSOC));
}
} else {
// dump errors from setting variable
var_dump($conn->error); 
}

对我来说，结果转储如下所示：

array (size=3)
0 => 
array (size=4)
'rank' => string '1' (length=1)
'id' => string '2' (length=1)
'username' => string 'Bar' (length=3)
'overall' => string '1000' (length=4)
1 => 
array (size=4)
'rank' => string '2' (length=1)
'id' => string '6' (length=1)
'username' => string 'Tom' (length=3)
'overall' => string '7' (length=1)
2 => 
array (size=4)
'rank' => string '3' (length=1)
'id' => string '1' (length=1)
'username' => string 'Foo' (length=3)
'overall' => string '3' (length=1)