select * from test where city in (select city from test group by city having COUNT(*));
wangzhi
2017-10-20 09:24:04 +08:00
没有看明白 不知道是不是这种 select city,compan,count(city) as c from table group by city order by c;
zjp
2017-10-20 09:28:58 +08:00
SELECT t1.city, t1.company FROM your_table AS t1 JOIN (SELECT city, count(city) city_count FROM your_table GROUP BY city) AS t2 ON t2.city = t1.city ORDER BY city_count DESC, company;
写了好久…楼上都已经贴出来了→_→ 楼主这种自顶贴的做法真的很烦。没人回复也不代表就没人会。
zjp
2017-10-20 09:34:31 +08:00
@wangzhi 掉坑里了, 用 GROUP BY 语句会丢掉部分分组,每个 city 应该只保留一行。但应该保留哪一行没有定义,MySQL 某个版本后已经默认禁止这样的语句
1. select city, count(*) as cnt from table group by city
city cnt 合肥 3 上海 1 武汉 2 杭州 1
2. select a.city, a.company from table a left join (select city, count(*) as cnt from table group by city) b on a.city = b.city order by b.cnt desc, a.company