我得到了这个查询:
$characterinfoquery = "
SELECT
c.Name,
c.Level,
c.Sex,
c.Playtime,
c.KillCount,
c.DeathCount,
cl.Name AS clanName
FROM
Character AS c,
Account AS a,
ClanMember AS cm,
Clan AS cl
WHERE
c.AccountID = a.AccountID AND
c.CharacterID = cm.CharacterID AND
cm.ClanID = cl.ClanID AND
a.UserID='".mssql_real_escape_string($_SESSION['username'])."'
";
但我希望那些没有部落的成员也能看到,但不是部落名称,而是在部落名称应该在的地方写"-"。
这是我的while声明:
if(mssql_num_rows($characterinforesult) != 0){
$content = str_replace("%content%", file_get_contents("tpl/contents/characterinfo.html"), $content);
//Get character information
$search = array("%Name%", "%Level%", "%Sex%", "%Playtime%", "%KillDeath%", "%Clan%");
$rows = file_get_contents("tpl/contents/characterinfo_tr.html");
while($row = mssql_fetch_assoc($characterinforesult)){
if($row['KillCount'] != 0){
$KillDeath = round($row['KillCount']/$row['DeathCount'], 2);
}
else{
$KillDeath = "-";
}
$Playtime = $row['Playtime']/60;
$replace = array($row['Name'], $row['Level'], gender($row['Sex']), round($Playtime), $KillDeath, $row['clanName']);
$tr .= str_replace($search, $replace, $rows);
}
}
有人能帮我吗?
带内部联接的输出:
Name Level Sex Playtime K/D Ratio Clan
DragonDex 97 Male 375 min 0.22 Test
它显示了1行,而该帐户中有2个字符,1个有一个氏族,另一个没有。
是否需要左外部联接:
SELECT
c.Name,
c.Level,
c.Sex,
c.Playtime,
c.KillCount,
c.DeathCount,
coalesce( cl.Name, ' - ' ) AS clanName
FROM
Character AS c
inner join
Account AS a
on c.AccountID = a.AccountID
left outer join
ClanMember AS cm
on c.CharacterID = cm.CharacterID
left outer join
Clan AS cl
on cm.ClanID = cl.ClanID
WHERE
a.UserID='".mssq ...