php - MySQL 检索还包括查询未找到的行

Question

一个有 php 的网页，可以从 mysql 数据库检索数据。我想知道如何检索查询中未找到的行。像这样的事情

那些在“玩家”列下有 0 的人意味着他们没有交易，假设数据是 2014 年。这是一种在 MS Excel 上制作的对特工的报告/监控。我想改善他们的生活，因此，我正在创建一个与此等效的网页，以便随时随地通过我们的 VPN 访问它。对于小型企业来说这还不错。

什么 SQL 查询可以实现此目的？

到目前为止我的 Sql 查询

SELECT COALESCE(b.`a_id`,'TOTAL') AS 
ALLGAMES, a.`risk`, 
COUNT(DISTINCT b.`player_id`) AS 'Player',
COUNT(`bet_id`) AS 'Qty',
SUM(`bet_amount`) AS Bet,
SUM(b.`zen_ss`) AS Gain,
SUM(`agent_ss`) AS AgentGain,
SUM(`sa_ss`) AS SAGain FROM `bet` b 
LEFT JOIN `agent` a ON b.`a_id`=a.`a_id` 
LEFT JOIN `player` p ON b.`player_id`=p.`player_id` 
LEFT JOIN `match` m ON b.`match_id`=m.`match_id` 
LEFT JOIN `game_type` gt ON b.`game_type_id`=gt.`game_type_id` 
LEFT JOIN `category` c ON b.`league_id`=c.`league_id` 
LEFT JOIN `bet_type` bt ON b.`bet_type_id`=bt.`bet_type_id` 
WHERE `bet_id`>0 GROUP BY b.`a_id` WITH ROLLUP

Answer 1

查询中的第一个表应该是代理表，查询的分组依据也应更改为代理 ID，所有其他左连接都应位于代理表之后。

SELECT COALESCE(b.`a_id`,'TOTAL') AS 
ALLGAMES, a.`risk`, 
COUNT(DISTINCT b.`player_id`) AS 'Player',
COUNT(`bet_id`) AS 'Qty',
SUM(`bet_amount`) AS Bet,
SUM(b.`zen_ss`) AS Gain,
SUM(`agent_ss`) AS AgentGain,
SUM(`sa_ss`) AS SAGain FROM `agent` a
LEFT JOIN `bet` b ON b.`a_id`=a.`a_id` AND `b`.`bet_id`>0
LEFT JOIN `player` p ON b.`player_id`=p.`player_id` 
LEFT JOIN `match` m ON b.`match_id`=m.`match_id` 
LEFT JOIN `game_type` gt ON b.`game_type_id`=gt.`game_type_id` 
LEFT JOIN `category` c ON b.`league_id`=c.`league_id` 
LEFT JOIN `bet_type` bt ON b.`bet_type_id`=bt.`bet_type_id` 
GROUP BY a.`a_id`