脚本宝典收集整理的这篇文章主要介绍了php – 在MySQL中,如何选择空记录，脚本宝典觉得挺不错的，现在分享给大家，也给大家做个参考。
MysqL> desc customers; +---------------------+-------------+------+-----+---------+-------+ | Field | TyPE | Null | Key | Default | Extra | +---------------------+-------------+------+-----+---------+-------+ | hash | vArchar(32) | NO | PRI | NULL | | | date_joined | date | NO | | NULL | | | agent_code | int(5) | NO | UNI | | | +---------------------+-------------+------+-----+---------+-------+ MysqL> desc persons; +--------------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------------------+-------------+------+-----+---------+-------+ | agent_code | int(5) | NO | UNI | | | | team_id | int(2) | YES | | 0 | | | hash | VARchar(32) | NO | PRI | NULL | | +--------------------+-------------+------+-----+---------+-------+ MysqL> desc teams; +--------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------+-------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(20) | YES | | NULL | | | leader | varchar(32) | NO | UNI | NULL | | +--------+-------------+------+-----+---------+----------------+
我正在使用的SQL查询如下：
SELECT COUNT(`customers`.`agent_code) AS `customer_count`,`teams`.`name` From `customers` JOIN `persons` ON `customers`.`agent_code` = `persons`.`agent_code` JOIN `teams` ON `persons`.`team_id` = `teams`.`id` group by `teams`.`name`
它显示以下信息：
+----------------+--------+ | customer_count | name | +----------------+--------+ | 3 | Team 1 | +----------------+--------+
但是,我希望看到数据库中所有团队的“customer_count”,即使给定团队的customer_count为null(或为零).我的数据库中有15个团队,所以我希望看到类似的东西：
+----------------+--------+ | customer_count | name | +----------------+--------+ | 3 | Team 1 | | 0 | Team 2 | | 0 | Team 3 | | 0 | Team 4 | +----------------+--------+
我试图执行以下Query的一些变体,但我总是得到一个错误,说OUTER JOIN的语法不正确,即使我已阅读文档,但它是正确的.
SELECT COUNT( `customers`.`agent_code` ) AS `customer_count`,`teams`.`name` From `customers` LEFT JOIN `persons` ON `customers`.`agent_code` = `persons`.`agent_code` LEFT OUTER JOIN `teams` ON `persons`.`team_id` = `teams`.`id` GROUP BY `teams`.`name`
SELECT COUNT(`customers`.`id`) AS `customer_count`,`teams`.`name` FROM `teams` JOIN `persons` ON `persons`.`team_id` = `teams`.`id` LEFT JOIN `customers` ON `customers`.`agent_code` = `persons`.`agent_code` GROUP BY `teams`.`name`
更新：如果您有空团队,则需要在人员上设置左连接
SELECT COUNT(`customers`.`id`) AS `customer_count`,`teams`.`name` FROM `teams` LEFT JOIN `persons` ON `persons`.`team_id` = `teams`.`id` LEFT JOIN `customers` ON `customers`.`agent_code` = `persons`.`agent_code` GROUP BY `teams`.`name`
以上是脚本宝典为你收集整理的php – 在MySQL中,如何选择空记录全部内容，希望文章能够帮你解决php – 在MySQL中,如何选择空记录所遇到的问题。
本图文内容来源于网友网络收集整理提供，作为学习参考使用，版权属于原作者。
如您有任何意见或建议可联系处理。小编QQ：384754419，请注明来意。