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

求 mysql 大神,请教一个句子。

  •  
  •   gonethen · 2018-04-04 11:38:41 +08:00 · 4526 次点击
    这是一个创建于 2186 天前的主题,其中的信息可能已经有所发展或是发生改变。

    现在有 3 个表或结果集:

    • 代理表 m
    • 用户月度流水结果集 ac
    • 代理月度流水结果集 ma

    做 LEFT JOIN 查询,看结果应该是重复了好几次,实在不知道问题在哪,该怎么改了。 特来请教 v2 的大神们,到底是哪里出错了,该怎么改,有没有可以优化的地方。 多谢啦!

    sql 如下:

    SELECT
        m.id,
        ...
        sum(ac.字段),
        ...
        sum(ma.字段),
        ...
    FROM m
    LEFT JOIN
        ac
    ON ac.up_id IN (SELECT m1.id FROM manager AS m1 WHERE m1.subCode = m.subCode)
    LEFT JOIN
        ma
    ON m.subCode = ma.subCode
    WHERE
        m.status = '0'
        ...
    GROUP BY
        m.id
    

    ps:如果把 ac 和 ma 拆分开,形成两个 sql,结果都是对的,为什么合起来用 LEFT JOIN 就不对了呢

    第 1 条附言  ·  2018-04-04 14:26:09 +08:00
    期望结果:
    用一条 sql
    查询所有顶级代理下的所有子代理(无下限)的月度流水
    和所有子代理的所有用户的月度流水
    26 条回复    2018-04-04 23:52:43 +08:00
    eluotao
        1
    eluotao  
       2018-04-04 12:11:03 +08:00 via iPhone
    写错 sql 了
    gonethen
        2
    gonethen  
    OP
       2018-04-04 14:13:09 +08:00
    @eluotao 应该怎么写啊?求指教
    BlackGrasshopper
        3
    BlackGrasshopper  
       2018-04-04 14:18:17 +08:00
    你并没有写 ac 表和其它标的关系,ON ac.up_id IN (SELECT m1.id FROM manager AS m1 WHERE m1.subCode = m.subCode)应该写好对应关系写在 where 里
    gonethen
        4
    gonethen  
    OP
       2018-04-04 14:23:06 +08:00
    @BlackGrasshopper ac.up_id 就是 m.id ,我这个 ON 条件是想查出来某个代理下的所有子代理下的所有用户,这个写到 where 里怎么 group by 呢?
    canbingzt
        5
    canbingzt  
       2018-04-04 14:25:56 +08:00
    感觉可以写 2 条 left join 吧
    left join manager m1 on m1.subCode=m.subCode
    left join ac on ac.up_id=m1.id
    gonethen
        6
    gonethen  
    OP
       2018-04-04 14:31:26 +08:00
    @canbingzt 我试了一下,确实可以分成两个,但结果还是不对,大神能不能加好友指导一下,nickname 即我微信
    wjpdev
        7
    wjpdev  
       2018-04-04 14:34:31 +08:00
    你若是给个完整的,即便是错误的 sql,我都好解答一点,里面的... 哪个晓的省略的是个什么鬼,Biu~ 丢一个参考答案:

    select m.id, sum(ac_字段), sum(ma_字段) from(
    select m.id,ac.字段 as ac_字段, 0 as ma_字段 from m left join ac on m.status = '0' and ... and ac.up_id in (SELECT m1.id FROM manager AS m1 WHERE m1.subCode = m.subCode)
    union
    select m.id, 0 as ac_字段, ma.字段 as ma_字段 from m left join ma on m.status = '0' and ... and m.subCode = ma.subCode
    ) C where m.status = '0' group by m.id;

    多调试,实践出真知.
    begga
        8
    begga  
       2018-04-04 14:36:09 +08:00
    SELECT
    m.id,
    ...
    SUM(ac.字段),
    ...
    SUM(ma.字段),
    ...
    FROM m ,ac ,ma
    WHERE m.id=ac.up_id AND m.id=ma.id AND m.subCode ='0' AND ma.subCode='0'
    GROUP BY m.id
    wjpdev
        9
    wjpdev  
       2018-04-04 14:37:29 +08:00
    那个,where m.status='0' 不要了. 忘了删
    gonethen
        10
    gonethen  
    OP
       2018-04-04 14:40:45 +08:00
    @wjpdev 字段有点涉及公司隐私,可否加个微信单独请教,nickname 即我微信
    begga
        11
    begga  
       2018-04-04 14:45:31 +08:00
    你这三张表?,manager 是什么表?
    gonethen
        12
    gonethen  
    OP
       2018-04-04 14:46:19 +08:00
    @begga 就是 m 表
    begga
        13
    begga  
       2018-04-04 14:53:26 +08:00
    SELECT
    m.id,
    ...
    sum(ac.字段),
    ...
    sum(ma.字段),
    ...
    FROM m ,ac ,ma
    where m.id=ac.up_id and m.id=ma.id and m.subCode = ma.subCode and m.status = '0'
    GROUP BY m.id


    m.id=ac.up_id and m.id=ma.id (把三张表关联成一张表)然后就当作单表操作了。
    linyinma
        14
    linyinma  
       2018-04-04 15:06:13 +08:00
    对了才怪,你想统计用户月度流水结果集 ac, 代理月度流水结果集 ma,使用 left join
    sum(ac.字段) 应该是正确的,sum(ma.字段)小于正确值; 因为第一次 left join 的结果集应该小于正确的结果集

    两种解决办法
    ( 1 )分开计算啊;
    ( 2 )采用 FULL JOIN
    linyinma
        15
    linyinma  
       2018-04-04 15:09:47 +08:00
    sorry,我刚描述的采用 FULL JOIN 不对, 因为你最好 grup by m.id, FULL JOIN 导致结果集 m.id , 统计小于正确结果集
    gonethen
        16
    gonethen  
    OP
       2018-04-04 15:12:20 +08:00
    @begga 就是不用 left join 是吧?我试了,查询结果还是不对
    begga
        17
    begga  
       2018-04-04 15:25:48 +08:00
    @gonethen 需求我没有看懂。- -
    gonethen
        18
    gonethen  
    OP
       2018-04-04 15:30:15 +08:00
    @linyinma
    你好像说到点上了,
    我知道可以分两个 sql 来写,但是只能分开计算吗?
    我 sum 出来的数据都大于分开计算的数据,
    假设分开计算的数据是正确数据(我相信是正确的)
    ac 正确数据的 7 倍,ma 是正确数据的 14 倍

    这样描述问题,您看能发现是什么问题吗?
    gonethen
        19
    gonethen  
    OP
       2018-04-04 15:31:08 +08:00
    @begga 能加个微信单独请教吗,nickname 即我微信
    linyinma
        20
    linyinma  
       2018-04-04 15:57:01 +08:00
    实在要写成一条那就这样吧:
    SELECT
    id,
    T1.acX1
    ...
    T2.maXn
    FROM
    (
    SELECT
    m.id,
    sum(ac.字段 1) as acX1,
    ...
    sum(ac.字段 n) as acXn,
    FROM m
    LEFT JOIN
    ac
    ON ac.up_id IN (SELECT m1.id FROM manager AS m1 WHERE m1.subCode = m.subCode)
    GROUP BY m.id HAVING m.status = '0'

    ) as T1
    FULL JOIN
    (
    SELECT
    m.id,
    sum(ma.字段 1) as maX1,
    ...
    sum(ma.字段 n) as maXn,
    FROM m
    LEFT JOIN
    ma
    ON m.subCode = ma.subCode
    GROUP BY m.id HAVING m.status = '0'
    ) as T2
    ON T1.id = T2.id
    XOXO360
        21
    XOXO360  
       2018-04-04 16:58:46 +08:00
    你这明显多对多关系。数据逻辑先理清楚再写 sql,只能说同一个代理多条,同一个用户多条。匹配不就翻倍了?
    gonethen
        22
    gonethen  
    OP
       2018-04-04 17:19:50 +08:00
    @linyinma 我按照你说的改了一下,不知道为什么用 FULL JOIN 会报语法错误,把 FULL 改成 LEFT 就没语法错误。。。
    @XOXO360 确实是这个道理,但是现在我不知道怎么改,求大神指点
    gonethen
        23
    gonethen  
    OP
       2018-04-04 17:31:09 +08:00
    @linyinma Mysql 不支持 FULL JOIN。。。
    XOXO360
        24
    XOXO360  
       2018-04-04 17:38:53 +08:00   ❤️ 1
    @gonethen 新手都喜欢一条简单 sql 出来,可以彰显技术。事实上不是所有的业务都行的,尤其是 mysql,根据逻辑来写 sql。没看到具体表和业务逻辑,数据结构,无法判断,硬要凑,就子查询拼起来,也就是你说的一条 sql。还有一种办法,你在 select 里面写查询。这个具体看你的结果集多大咯。
    gonethen
        25
    gonethen  
    OP
       2018-04-04 17:52:49 +08:00
    @XOXO360 谢谢指导。我现在也改成了子查询拼接
    akira
        26
    akira  
       2018-04-04 23:52:43 +08:00
    做汇总查询的时候,个人习惯是尽量先做汇总再来 join, 这样虽然最终代码量可能会多几行,但是条理很清理,最重要的是,每一部分要排查错误都非常容易。

    就你这个例子的话,我会先分别做 sum 操作,然后再对出来的结果表做 join 操作
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   我们的愿景   ·   实用小工具   ·   2657 人在线   最高记录 6543   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 28ms · UTC 15:21 · PVG 23:21 · LAX 08:21 · JFK 11:21
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.