捣鼓一下午到现在还没解决的一句简单 SQL 语句求赐教：两张表关联查询求总数降序排列

[jookr]

表 member


表 city



查表 [ member as m ] 的数据总量 [ count(*) as total ]
顺带一起展示 city 表的 [ c.id,c.city_name,c.pinyin ]
关联另外一张表 [ left join city as c ]
关系是 member 的 m.from 关联 city 的 c.pinyin [ on m.from=c.pinyin ]
按照 c.pinyin 分组 [ group by c.pinyin ]
按照 total 降序排列 [ order by total desc ]


自己测试过以下语句
select c.id,c.city_name,c.pinyin,count(*) as total
from `city` as c
left join `member` as m on m.from = c.pinyin
group by c.pinyin
order by total desc
空记录的深圳本该是 0 ，但是查出来竟然是 1 。


select c.id,c.city_name,c.pinyin,count(*) as total
from `city` as c
left join `member` as m on m.from = c.pinyin
group by c.id
order by total desc
空记录的深圳本该是 0 ，但是查出来竟然是 1 。

select c.id,c.city_name,c.pinyin,count(*) as total
from `city` as c
left join `member` as m on m.from = c.pinyin
group by c.city_name
order by total desc
空记录的深圳本该是 0 ，但是查出来竟然是 1 。

以上三条 sql 结果一样，如下图

=================================


select c.id,c.city_name,c.pinyin,count(*) as total
from `member` as m
left join `city` as c on m.from = c.pinyin
group by m.from
order by total desc
少了 0 数据的深圳，数据不全。

select c.id,c.city_name,c.pinyin,count(*) as total
from `member` as m
left join `city` as c on m.from = c.pinyin
group by c.city_name
order by total desc
少了 0 数据的深圳，数据不全。

select c.id,c.city_name,c.pinyin,count(*) as total
from `member` as m
left join `city` as c on m.from = c.pinyin
group by c.pinyin
order by total desc
少了 0 数据的深圳，数据不全。

以上三条 sql 结果一样，如下图


=====================================
--
-- 表的结构 `city`
--

CREATE TABLE IF NOT EXISTS `city` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`city_name` varchar(30) NOT NULL,
`pinyin` varchar(30) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=6 ;


INSERT INTO `city` (`id`, `city_name`, `pinyin`) VALUES
(1, '北京', 'beijing'),
(2, '上海', 'shanghai'),
(3, '广州', 'guangzhou'),
(4, '深圳', 'shenzhen'),
(5, '未知', '');
================================

--
-- 表的结构 `member`
--

CREATE TABLE IF NOT EXISTS `member` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(30) NOT NULL,
`from` varchar(30) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=10 ;



INSERT INTO `member` (`id`, `name`, `from`) VALUES
(1, 'a1', 'beijing'),
(2, 'a2', 'beijing'),
(3, 'a3', 'beijing'),
(4, 'a4', 'shanghai'),
(5, 'a5', 'shanghai'),
(6, 'a6', 'guangzhou'),
(7, 'a7', ''),
(8, 'a8', ''),
(9, 'a9', '');
==================================
请问如何写这条 sql 语句才能正确的查询如第一段想要的结果呢？

[terence4444]

粗看了一下，你把 city 表放左边似乎是不对的，应该挂右表。

[cxbig]

跟 city 相关的信息都放 city 表，和 member 的关联只给一个 city_id 。
统计的时候 city 做主表，连上 member ， count 一下就好。

[alex321]

SELECT `member`.*,`city`.*,count(`member`.id) AS total
FROM member
RIGHT JOIN city ON `member`.`from`=`city`.`pinyin`
GROUP BY `city`.`id`
ORDER BY total DESC

你要统计的是按照城市来，请使用 right join 。。

[jookr]

原来如此 谢谢各位
看来得重温 mysql 基础知识了

[soolby]

过来问问楼主你还健身吗？