mysql - 将计数语句与现有查询结合起来

Question

已有查询

select first_name,last_name,email_address,name,captain_id,paid,p.player_num,league_id 
from stats_player p
left join  stats_player_team t on p.player_num=t.player_num 
left join  stats_team s on t.team_id=s.team_num 
where season_id=2 and name is not NULL 
order by league_id,s.captain_id,name,first_name;

我想添加此内容以在结果中创建另一列:

select count(*) from (select * from stats_results
where season =2 and player1_num=123 group by week)as weeks_played;

player1_num 实际上是 t.player_num

的循环

问题:这可以通过一条语句完成吗？

如果没有，我怎样才能将新的语句合并到下表中输出？

$results=mysql_query($query);
print "<table border=1>";
print "<tr><td>First Name</td><td>Last Name</td><td>captain</td><td>Email?</td><td>League</td><td>Team</td><td>pay</td><td>wks played</td></tr>";
while ($row=mysql_fetch_assoc($results)){
    if($row['email_address'] != NULL ){
    $email='y';
    }
else {
    $email='n';
    }
if($row[captain_id]==$row[player_num]){ $iscapt="Captain"; }
    else{$iscapt="";}
$paid=$row[paid];
if($paid){$playpaid="<td bgcolor=#99ff33>paid</td>";}
    else {$playpaid="<td bgcolor=#ff6633>not paid</td>";}
printf ("<tr><td>%s</td><td> %s</td><td>%s</td><td>%s</td><td>%s</td></td><td>%s</td>%s \n</tr>", $row['first_name'],$row['last_name'],$iscapt,$email,$row['league_id'],$row['name'],$playpaid);

}
print "</table>";

Answer 1

这是一个标量查询(最多返回 1 行)，因此可以将其作为子查询放置在 SELECT 表达式列表中。
一个例子:

select first_name,
       last_name,
       email_address,
       name,
       captain_id,
       paid,
       p.player_num,
       league_id,

       (   select count(*) from (select * from stats_results
           where season =2 and player1_num=123 group by week)as weeks_played
       ) As column_name

from stats_player p
left join  stats_player_team t on p.player_num=t.player_num 
left join  stats_team s on t.team_id=s.team_num 
where season_id=2 and name is not NULL 
order by league_id,s.captain_id,name,first_name;

顺便说一句，这个查询可以简化为:

select count(distinct week) 
from stats_results
where season =2 and player1_num=123