V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
miv
V2EX  ›  程序员

求助,如何写这种业务 sql 比较好

  •  
  •   miv · 2019-11-16 22:03:54 +08:00 · 2817 次点击
    这是一个创建于 1594 天前的主题,其中的信息可能已经有所发展或是发生改变。

    做公司项目遇到一个问题。 是这样的,需要统计一些数据 a 表有字段:机构编码,渠道编码,业务值等 机构表 b 有字段:机构编码,机构名称,父级机构编码等 a 表的数据比如:100789、1、100 然后 b 表数据: 1007、广州、100, 10078、天河某区、100 等数据 因为要统计 a 表数据,并且要显示机构名称。所以 a 表 b 表需要关联 此时使用 like 进行关联匹配(机构编码无法直接等于去关联) 使用 sql 如 a.机构编码 like b.机构编码% 可是这样 a 一条数据对于了 2 个机构,数据重复了 怎么破?

    24 条回复    2019-11-18 21:41:12 +08:00
    miv
        1
    miv  
    OP
       2019-11-16 22:11:14 +08:00 via iPhone
    想上面的写法,汇总的数据会变多
    需要把广州的数据,减去广州天河某区的数据才对。
    因为匹配天河某区的数据也匹配到广州了,所以广州还要减去天河某区的数据,最后的数据才对。

    造成这个问题是,
    而且机构编码那样子。like 一下就匹配多了
    miv
        2
    miv  
    OP
       2019-11-16 22:13:00 +08:00 via iPhone
    想问下这种机构编码的设计正常吗
    求大佬
    miv
        3
    miv  
    OP
       2019-11-16 22:17:47 +08:00 via iPhone
    在线等
    miv
        4
    miv  
    OP
       2019-11-16 22:22:13 +08:00 via iPhone
    出现了这个问题,然后我相关的统计代码就需要做一个剔除操作,感觉很麻烦。
    而且剔除的机构编码还是硬编码
    得在代码里面写广州需要提出广州天河某区等等
    感觉。。。
    crclz
        5
    crclz  
       2019-11-17 00:18:45 +08:00
    题目描述太少。隐隐约约感觉表 B 设计的有问题啊。

    机构编码 | 机构名称 | 父级机构编码
    - - -
    1007、广州、100
    10078、天河某区、100

    这两行属于相同的业务概念?
    reus
        6
    reus  
       2019-11-17 00:33:03 +08:00
    a 和 b 不需要关联,用子查询去取名字啊

    select *, (
    select string_agg(name, '')

    )
    from a
    aguesuka
        7
    aguesuka  
       2019-11-17 00:33:51 +08:00 via Android   ❤️ 1
    规范的话只能用表 a 的机构编码,或者父级编码去 join 表 b 的机构编码。能提出这个问题说明数据库最好玩重新设计
    reus
        8
    reus  
       2019-11-17 00:34:51 +08:00
    a 和 b 不需要关联,用子查询去取名字啊

    select *, (
    select string_agg(name, '')
    from b
    where index(b, a) = 0
    order by length(code) asc
    ) as name
    from a

    不记得 mysql 的函数名了,大概意思懂就行。
    reus
        9
    reus  
       2019-11-17 00:35:37 +08:00   ❤️ 1
    数据表设计没大问题,只是你想错了,从 b 表取名字要用子查询,不要用关联
    YUyu101
        10
    YUyu101  
       2019-11-17 00:38:45 +08:00 via Android
    所以你到底要不要天河某区,如果要的话就代码里弄,不要的话就 sql 里 join 名字不带区的第一个,如果名字也没有规则,有的要省有的要市有的要区,那这什么奇葩业务啊,硬编码吧。
    miv
        11
    miv  
    OP
       2019-11-17 08:15:47 +08:00 via iPhone
    @reus 看不太明白,可以详细介绍吗?如果可以这样那比较好
    miv
        12
    miv  
    OP
       2019-11-17 08:16:08 +08:00 via iPhone
    @YUyu101 需要的
    miv
        13
    miv  
    OP
       2019-11-17 08:19:36 +08:00 via iPhone
    @crclz 嗯,有点这个意思,所以 like 天河某区就一定 like 到广州了,数据一对二重复了
    miv
        14
    miv  
    OP
       2019-11-17 08:31:21 +08:00 via iPhone
    用的是 oracle,Java
    miv
        15
    miv  
    OP
       2019-11-17 08:35:12 +08:00 via iPhone
    @reus 大佬意思是,a 表拿机构名字用行内子查询,like 一下,然后根据 code 长度,优先匹配机构编码长的吗
    shakoon
        16
    shakoon  
       2019-11-17 08:46:51 +08:00 via Android
    a 表机构号都是 4 位的是吧?那很简单啊,select a.*, b.机构名 from a left join b on a.机构号=b.机构号 and length(b.机构号)=4
    TinyKube
        17
    TinyKube  
       2019-11-17 08:46:56 +08:00
    2 次查询就好了,先查 B 表需要的数据,统计 B 表的关联字段,再查询得到 A 表数据,再 Join 到 B 表
    shakoon
        18
    shakoon  
       2019-11-17 08:49:08 +08:00 via Android
    哦,a 不是 4 位啊,改一下,substr(a.机构号,0,4)=b.机构号
    miv
        19
    miv  
    OP
       2019-11-17 11:26:53 +08:00 via iPhone
    SELECT
    BRANCH_CODE,
    SUM(SIGN_PREM_S) SIGN_PREM_S,
    (
    SELECT
    t.branch_code
    FROM
    SYSTEM_BRANCH t
    WHERE
    PARENT_BRANCH = '01'
    AND g.branch_code LIKE t.BRANCH_CODE || '%'
    AND rownum = 1
    ORDER BY
    length(branch_code) DESC
    ) as branch_code
    FROM
    STAT_REPORT_HOME g
    WHERE
    TO_CHAR (g.ANNUAL_DAY, 'yyyymmdd') = '20191116'
    GROUP BY
    BRANCH_CODE
    这个是一部分 sql,按照楼上说的
    用行内子查询,不过 oracle 提示 order by 缺少右括号
    求助
    miv
        20
    miv  
    OP
       2019-11-17 12:03:50 +08:00 via iPhone
    问题已解决,参考楼上老哥的方法,行内子查询。
    解决过程中遇到 2 个问题。
    1,子查询使用 order by 提示缺少右括号,需要用 select 包装一层
    2,如上面的 sql,按照 1 做法,还是出现 g.branch-code 找不到的问题,此时需要把这个往外提一层
    最终 sql 给大家参考,同时感谢 v 友和 qq 好朋友的帮助。
    谢谢
    065535
        21
    065535  
       2019-11-18 11:26:31 +08:00   ❤️ 1
    上面的方法可以解决数据查询问题,但未来可能会遇到性能瓶颈。终极建议:业务上增加一个冗余列做关联,不要用 like 了。
    miv
        22
    miv  
    OP
       2019-11-18 12:05:15 +08:00 via iPhone
    @065535 有道理,星期天加班按那种方法搞,可以没错,性能上是一个问题,而且特别是统计业务这块,写出来 sql 我自己都绕晕了,而且很复杂。
    早知道还是用实体类分步骤去弄了。
    数据结构这方面没权力改,老哥建议很好。
    lumious
        23
    lumious  
       2019-11-18 13:35:31 +08:00   ❤️ 1
    使用 b 表作为主表,对 a 表的机构编码进行截取汇总关联到主表中
    从楼主给的数据看,市一级(广州市)的编码是 4 位,区一级(白云区)的编码是 5 位
    要统计到区一级,sql 类似于这样:
    select b.地区编码,statistic.* from b,(select substr(机构编码,0,5) 地区编码,sum(业务值) group by substr(机构编码,0,5)) statistic where b.地区编码=statictic.地区编码(+) and b.地区编码 like '_____'
    这个是 oracle 的写法,mysql 改成 left join 就行
    miv
        24
    miv  
    OP
       2019-11-18 21:41:12 +08:00
    @lumious 机构有可能是更低一级,截取不太可行.所以,今天,现在还是不考虑复杂 sql 了,今天用实体类,查询出全部数据,在一步步处理
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   我们的愿景   ·   实用小工具   ·   1091 人在线   最高记录 6543   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 28ms · UTC 18:52 · PVG 02:52 · LAX 11:52 · JFK 14:52
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.