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
lxjsmdc
V2EX  ›  MySQL

捣鼓一上午还写不出一条经常用到的只查一张表的 SQL 语句 求高手赐教doge

  •  
  •   lxjsmdc · 2015-12-10 10:54:27 +08:00 · 2994 次点击
    这是一个创建于 3254 天前的主题,其中的信息可能已经有所发展或是发生改变。
    只查一张表,结构和数据如下


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

    INSERT INTO `member` (`id`, `name`, `tuijianren_id`) VALUES
    (1, 'aaa', 0),
    (2, 'bbb', 1),
    (3, 'ccc', 1),
    (4, 'ddd', 1),
    (5, 'eee', 2),
    (6, 'fff', 2),
    (7, 'ggg', 3);
    =========================

    查询结果需求四个字段如下:
    [一] id
    [二] name
    [三] tuijian_name
    [四] total

    id 和 name 是本表中所有记录的原有字段

    tuijianren_name 是表中 tuijianren_id 对应本表 id 得出的 name,如果 tuijianren_id 是 0 或者 tuijianren_id 没有对应的 id 记录则为 null

    total 是统计出本表中每条记录的 id 出现在其他记录 tuijianren_id 的总数

    最终理想的查询结果如下


    我左连接又换右连接怎么写都不对

    SELECT m.id, m.name, COUNT( `m`.id ) AS total, m2.name AS tuijian_name
    FROM member m
    RIGHT JOIN member m2 ON m.tuijianren_id = m2.id
    GROUP BY `m2`.id
    LIMIT 0 , 30

    请问如何写这条语句满足这个需求?
    请老司机赐教一下 谢谢!!!
    14 条回复    2015-12-10 21:34:20 +08:00
    syyy
        1
    syyy  
       2015-12-10 11:04:52 +08:00
    感觉应该是 count 左联
    klesh
        2
    klesh  
       2015-12-10 11:05:36 +08:00   ❤️ 1
    SELECT m.*, m2.name AS tuijian_name, (SELECT COUNT(*) FROM member m3 WHERE m3.tuijianren_id = m2.id) AS total
    FROM member m
    JOIN member m2 ON (m.tuijianren_id = m2.id)
    LIMIT 0, 30
    lxjsmdc
        3
    lxjsmdc  
    OP
       2015-12-10 11:33:15 +08:00
    @klesh 灰常感谢

    但是按照你给的语句, tuijianren_id=0 的记录就不显示出来了(记录不全)
    能否再帮忙修改一下吗
    谢谢
    cxe2v
        4
    cxe2v  
       2015-12-10 11:36:23 +08:00   ❤️ 1
    @lxjsmdc 你 id 本来就没有=0 的啊
    lynx
        5
    lynx  
       2015-12-10 11:38:12 +08:00   ❤️ 1
    select m.id, m.name, m.tuijianren_id, q.c as total from member as m
    left outer join (
    select tuijianren_id, count(*) as c from member
    group by tuijianren_id
    ) as q on q.tuijianren_id = m.id

    上面的 m.tuijian 再查一层换成 name 就可以了
    lxjsmdc
        6
    lxjsmdc  
    OP
       2015-12-10 11:39:18 +08:00
    @cxe2v 那怎么办 原本总共 7 条数据,现在只能查出 6 条
    chaegumi
        7
    chaegumi  
       2015-12-10 11:41:29 +08:00   ❤️ 1
    select A.*, B.name as tuijian_name,(select count(C.id) from member C where C.tuijianren_id=A.id) as total from member A LEFT JOIN member B on A.tuijianren_id=B.id
    jy02201949
        8
    jy02201949  
       2015-12-10 12:07:06 +08:00   ❤️ 1
    要是我肯定会先
    INSERT INTO 'member' ('id', 'name', 'tuijianren_id') VALUES (0, 'null', 0);
    曲线救国。。。
    sun2920989
        9
    sun2920989  
       2015-12-10 12:09:26 +08:00   ❤️ 1
    自连接加子查询 但是我应该会选择分拆 sql 在语言层做合并数组
    xuyinan503
        10
    xuyinan503  
       2015-12-10 12:14:07 +08:00   ❤️ 1
    select a.id,a.name,b.name,count(c.id) as count
    from member a
    left join member b
    left join member c
    where a.tuijianren_id=b.id
    and a.id=c.tuijianren_id

    这样子就对了啊
    感觉没那么复杂吧
    xuyinan503
        11
    xuyinan503  
       2015-12-10 12:16:02 +08:00   ❤️ 1
    select a.id,a.name,b.name,count(c.id) as count
    from member a
    left join member b
    on a.tuijianren_id=b.id
    left join member c
    on a.id=c.tuijianren_id
    group by a.id,a.name,b.name

    好久没写还是写错了
    tSQghkfhTtQt9mtd
        12
    tSQghkfhTtQt9mtd  
       2015-12-10 12:29:35 +08:00   ❤️ 1
    简直无法接受用 id 和 name 的时候突然来一个 tuijianren
    lxjsmdc
        13
    lxjsmdc  
    OP
       2015-12-10 13:44:04 +08:00
    原来要 join 两次或者外查询或者子查询
    我想得太简单了
    谢谢楼上各位
    billgreen1
        14
    billgreen1  
       2015-12-10 21:34:20 +08:00
    @liwanglin12 是的呀,最讨厌汉语拼音命名的了
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   实用小工具   ·   4876 人在线   最高记录 6679   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 22ms · UTC 05:37 · PVG 13:37 · LAX 21:37 · JFK 00:37
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.