V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
MySQL 5.5 Community Server
MySQL 5.6 Community Server
Percona Configuration Wizard
XtraBackup 搭建主从复制
Great Sites on MySQL
Percona
MySQL Performance Blog
Severalnines
推荐管理工具
Sequel Pro
phpMyAdmin
推荐书目
MySQL Cookbook
MySQL 相关项目
MariaDB
Drizzle
参考文档
http://mysql-python.sourceforge.net/MySQLdb.html
jookr
V2EX  ›  MySQL

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

  •  
  •   jookr · 2015-12-08 21:25:25 +08:00 · 2530 次点击
    这是一个创建于 3271 天前的主题,其中的信息可能已经有所发展或是发生改变。
    表 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 语句才能正确的查询如第一段想要的结果呢?
    5 条回复    2015-12-24 12:19:33 +08:00
    terence4444
        1
    terence4444  
       2015-12-08 21:34:40 +08:00   ❤️ 1
    粗看了一下,你把 city 表放左边似乎是不对的,应该挂右表。
    cxbig
        2
    cxbig  
       2015-12-08 21:38:37 +08:00   ❤️ 1
    跟 city 相关的信息都放 city 表,和 member 的关联只给一个 city_id 。
    统计的时候 city 做主表,连上 member , count 一下就好。
    alex321
        3
    alex321  
       2015-12-08 21:41:19 +08:00   ❤️ 1
    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
        4
    jookr  
    OP
       2015-12-08 21:47:56 +08:00
    原来如此 谢谢各位
    看来得重温 mysql 基础知识了
    soolby
        5
    soolby  
       2015-12-24 12:19:33 +08:00
    过来问问楼主你还健身吗?
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   实用小工具   ·   4857 人在线   最高记录 6679   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 24ms · UTC 05:41 · PVG 13:41 · LAX 21:41 · JFK 00:41
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.