联合查询把记录为零的数据也查出来

Question

*/
create database if not exists `test`;USE `test`;/*Table structure for table `mms_monthuserlog201105` */DROP TABLE IF EXISTS `mms_monthuserlog201105`;CREATE TABLE `mms_monthuserlog201105` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `userMobile` varchar(45) NOT NULL DEFAULT '',
  `gwID` varchar(10) NOT NULL DEFAULT '',
  `serviceType` varchar(12) NOT NULL DEFAULT '',
  `registerTime` datetime DEFAULT '0000-00-00 00:00:00',
  `cancelTime` datetime DEFAULT '0000-00-00 00:00:00',
  `channeltype` int(11) NOT NULL DEFAULT '0',
  `city_id` int(11) NOT NULL DEFAULT '-1',
  `town_id` int(11) NOT NULL DEFAULT '-1',
  `isDivide` int(3) NOT NULL DEFAULT '0',
  `memo` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `mms_monthUserLog201105_u` (`userMobile`)
) ENGINE=InnoDB AUTO_INCREMENT=512 DEFAULT CHARSET=latin1;/*Data for the table `mms_monthuserlog201105` */insert into `mms_monthuserlog201105` values (1,'13583318004','130306','hyyd','0000-00-00 00:00:00','2011-05-02 22:08:30',3,4,1,0,'1'),(2,'13603867476','130300','hyyd','0000-00-00 00:00:00','2011-05-02 01:52:21',3,4,1,0,'1'),(3,'13935886028','135302','dysp','0000-00-00 00:00:00','2011-05-01 09:31:12',4,4,1,0,'1'),(4,'15934090882','135302','dysp','0000-00-00 00:00:00','2011-05-01 11:44:20',4,4,3,0,'1'),(5,'15969383098','135302','dysp','0000-00-00 00:00:00','2011-05-01 11:26:49',4,4,2,0,'1'),(6,'15287908222','135302','dysp','0000-00-00 00:00:00','2011-05-02 16:01:43',4,4,1,0,'1'),(7,'13648764931','135302','dysp','0000-00-00 00:00:00','2011-05-02 00:46:31',4,4,1,0,'1'),(8,'15240808431','135302','dysp','0000-00-00 00:00:00','2011-05-02 00:45:26',4,4,1,0,'1'),(9,'13888210079','135302','dysp','0000-00-00 00:00:00','2011-05-01 15:27:35',4,4,1,0,'1'),(10,'18760821068','135302','dysp','0000-00-00 00:00:00','2011-05-02 00:01:09',4,4,1,0,'1'),(11,'18214278136','135302','dysp','0000-00-00 00:00:00','2011-05-02 01:47:17',4,2,1,0,'1'),(12,'15035369447','135302','dysp','0000-00-00 00:00:00','2011-05-01 08:13:50',4,1,2,0,'1'),(13,'15126954724','135302','dysp','0000-00-00 00:00:00','2011-05-01 02:14:09',4,3,1,0,'1'),(14,'15812299727','135302','dysp','0000-00-00 00:00:00','2011-05-02 01:57:58',4,4,1,0,'1');
select c.city_name ,count(c.city_name)  from  mms_monthuserlog201105 m  left join sys_city c  on c.city_id=m.city_id  group by m.city_id --这条语句只能查出有记录的信息，
我想把所有city_name显示出来没记录的count(c.city_name)显示为0 

Answer 1

sys_city没有建表SQL？
要求结果是什么

Answer 2

USE `test`;/*Table structure for table `sys_city` */DROP TABLE IF EXISTS `sys_city`;CREATE TABLE `sys_city` (
  `city_id` smallint(6) NOT NULL DEFAULT '0',
  `city_name` varchar(20) NOT NULL DEFAULT '',
  `reckoning_id` smallint(6) NOT NULL DEFAULT '0',
  `gwID_mobile` varchar(11) NOT NULL DEFAULT 'all',
  `miscSync_id` varchar(100) NOT NULL DEFAULT '',
  `miscSync_type` int(4) NOT NULL DEFAULT '0',
  `single_fee` int(11) DEFAULT NULL,
  PRIMARY KEY (`city_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;/*Data for the table `sys_city` */insert into `sys_city` values (1,'辽宁',240,'new_hnyd','ln',0,100),(2,'青海',971,'new_hnyd','qh',0,100),(3,'新疆',991,'new_hnyd','xj',0,100),(4,'陕西',290,'new_hnyd','sn',0,100),(5,'上海',210,'new_hnyd','sh',0,100),(6,'四川',280,'new_scyd','sc',0,100),(7,'山西',351,'new_hnyd','sx',0,100),(8,'山东',531,'new_hnyd','sd',0,100),(9,'天津',220,'new_hnyd','tj',0,100),(10,'西藏',891,'new_hnyd','xz',0,100),(11,'浙江',571,'new_zjyd','zj',0,100),(12,'云南',871,'new_hnyd','yn',0,100),(13,'内蒙古',471,'new_hnyd','nm',0,100),(14,'宁夏',951,'nxyd','nx',0,100),(15,'重庆',230,'new_hnyd','cq',0,100),(16,'安徽',551,'new_hnyd','ah',0,100),(17,'北京',100,'new_hnyd','bj',0,100),(18,'甘肃',931,'new_hnyd','gs',0,100),(19,'福建',591,'new_hnyd','fj',0,100),(20,'广西',771,'new_hnyd','gx',0,100),(21,'广东',200,'gdyd','gd',1,100),(22,'贵州',851,'new_hnyd','gz',0,100),(23,'河南',371,'new_hnyd','ha',0,100),(24,'河北',311,'new_hnyd','he',0,100),(25,'黑龙江',451,'hljyd','hl',0,100),(26,'海南',898,'new_hnyd','hi',0,100),(27,'湖南',731,'hunanyd','hn',0,100),(28,'湖北',270,'new_hnyd','hb',0,100),(29,'吉林',431,'new_hnyd','jl',0,100),(30,'江苏',250,'new_hnyd','js',0,100),(31,'江西',791,'new_hnyd','jx',0,100),(0,'未知',0,'all','',0,0),(-1,'未知',0,'all','',0,0);
结果是把sys_city的省份都列出来 记录填充为0

Answer 3

select c.city_name ,count(c.city_name) from sys_city c  left join  
mms_monthuserlog201105 m
on c.city_id=m.city_id group by m.city_id

Answer 4

这种普通的查询只能查出有记录的省份，我要把无记录的city_name跟count无记录的视为0显示出来

Answer 5

select a.city_name,count(b.city_id)
from sys_city a left join mms_monthuserlog201105 b on a.city_id=b.city_id
group by a.city_name

Answer 6

很奇怪，#7楼 不就是你要的结果吗？ 或者说#7楼不是你的结果，那你期望的结果是什么样？！mysql> select a.city_name,count(b.city_id)
    -> from sys_city a left join mms_monthuserlog201105 b on a.city_id=b.city_id
    -> group by a.city_name;
+-----------+------------------+
| city_name | count(b.city_id) |
+-----------+------------------+
| 辽宁      |                1 |
| 青海      |                1 |
| 新疆      |                1 |
| 陕西      |               11 |
| 上海      |                0 |
| 四川      |                0 |
| 山西      |                0 |
| 山东      |                0 |
| 天津      |                0 |
| 未知      |                0 |
| 西藏      |                0 |
| 浙江      |                0 |
| 云南      |                0 |
| 内蒙古    |                0 |
| 宁夏      |                0 |
| 重庆      |                0 |
| 安徽      |                0 |
| 北京      |                0 |
| 甘肃      |                0 |
| 福建      |                0 |
| 广西      |                0 |
| 广东      |                0 |
| 贵州      |                0 |
| 河南      |                0 |
| 河北      |                0 |
| 黑龙江    |                0 |
| 海南      |                0 |
| 湖南      |                0 |
| 湖北      |                0 |
| 吉林      |                0 |
| 江苏      |                0 |
| 江西      |                0 |
+-----------+------------------+
32 rows in set (0.00 sec)mysql>

Answer 7

select c.city_name ,count(c.city_name) from sys_city c left join   
mms_monthuserlog201105 m
on c.city_id=m.city_id group by c.city_id