Deep_Red
Deep_Red
采纳率50%
2016-10-10 09:13

一个关于sql语句的问题

5
已采纳

现在有两个表,A表和B表,A表左连接于B表并且根据B表一个字段筛选一部分数据,此时B表被取别名为b1,得到的结果显示在最终表的一个列中,假设这个列叫做b1。紧接着再次左连接,再根据相同字段筛选,但是这次筛选条件不同了,此时B表取别名为b2,得到的结果列假设叫b2,也显示在最终表中,并且和之前的b1是两个不同的列。这种情况的sql语句有没有办法组合起来,难道只能写重复代码进行两次左连接吗?

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享
  • 邀请回答

5条回答

  • rookie_liu_ToFly rookie_liu_ToFly 5年前

    你描述的这种场景我也遇到过,需要对一张表进行分区间汇总,比如:1000-5000,5000-10000,10000-20000等,吧汇总的结果放在一张表里面进行分析对比。union 也是可以的,但是效率太低。多次左连接的方法效率挺高的,别想其他的了。
    select a.branch_id,d.name 分公司,isnull(b.count1,0),isnull(b1.count2,0),isnull(b2.count3,0),
    isnull(b3.count4,0),isnull(b4.count5,0),isnull(b5.count6,0),
    isnull(b6.count7,0),isnull(b7.count8,0),isnull(b8.count9,0),
    isnull(b9.count10,0),isnull(b10.count11,0)
    from T_CONTRACT_ORDER a
    left join T_DEPT d on a.branch_id=d.seed
    left join (select branch_id,count(*)count1 from T_CONTRACT_ORDER where discount_money>=1000 and discount_money='2016-09-01' and CREATE_DATE<'2016-10-01'
    group by branch_id)b on a.branch_id=b.branch_id
    left join (select branch_id,count(*)count2 from T_CONTRACT_ORDER where discount_money>=5000 and discount_money='2016-09-01' and CREATE_DATE<'2016-10-01'
    group by branch_id)b1 on a.branch_id=b1.branch_id
    left join (select branch_id,count(*)count3 from T_CONTRACT_ORDER where discount_money>=10000 and discount_money='2016-09-01' and CREATE_DATE<'2016-10-01'
    group by branch_id)b2 on a.branch_id=b2.branch_id
    left join (select branch_id,count(*)count4 from T_CONTRACT_ORDER where discount_money>=20000 and discount_money='2016-09-01' and CREATE_DATE<'2016-10-01'
    group by branch_id)b3 on a.branch_id=b3.branch_id
    left join (select branch_id,count(*)count5 from T_CONTRACT_ORDER where discount_money>=30000 and discount_money='2016-09-01' and CREATE_DATE<'2016-10-01'
    group by branch_id)b4 on a.branch_id=b4.branch_id
    left join (select branch_id,count(*)count6 from T_CONTRACT_ORDER where discount_money>=40000 and discount_money<=49000 and data_type=2 and CREATE_DATE>='2016-09-01' and CREATE_DATE<'2016-10-01'
    group by branch_id)b5 on a.branch_id=b5.branch_id
    left join (select branch_id,count(*)count7 from T_CONTRACT_ORDER where discount_money>=50000 and discount_money='2016-09-01' and CREATE_DATE<'2016-10-01'
    group by branch_id)b6 on a.branch_id=b6.branch_id
    left join (select branch_id,count(*)count8 from T_CONTRACT_ORDER where discount_money>=100000 and discount_money='2016-09-01' and CREATE_DATE<'2016-10-01'
    group by branch_id)b7 on a.branch_id=b7.branch_id
    left join (select branch_id,count(*)count9 from T_CONTRACT_ORDER where discount_money>=300000 and discount_money='2016-09-01' and CREATE_DATE<'2016-10-01'
    group by branch_id)b8 on a.branch_id=b8.branch_id
    left join (select branch_id,count(*)count10 from T_CONTRACT_ORDER where discount_money>=500000 and discount_money='2016-09-01' and CREATE_DATE<'2016-10-01'
    group by branch_id)b9 on a.branch_id=b9.branch_id
    left join (select branch_id,count(*)count11 from T_CONTRACT_ORDER where discount_money>=800000 and discount_money<=990000 and data_type=2 and CREATE_DATE>='2016-09-01' and CREATE_DATE<'2016-10-01'
    group by branch_id)b10 on a.branch_id=b10.branch_id
    where a.data_type=2
    group by a.branch_id,d.name,b.count1,b1.count2,b2.count3,b3.count4,b4.count5,b5.count6,b6.count7,b7.count8,b8.count9,b9.count10,b10.count11
    order by d.name

    点赞 评论 复制链接分享
  • lyyzsms 蜗牛超车 5年前

    说得太抽象了,把表结构贴出来看看

    点赞 评论 复制链接分享
  • yicp123 yicp123 5年前

    这个sql的写法需要根据具体的需求而定,
    如果业务需求,可以用一次联接实现,那么就用一次联接,
    如果业务需求用一次联接实现不了,那么就用两次联接,
    先实现业务需求,再做优化。

    参考自:MySQL如何优化LEFT JOIN和RIGHT JOIN http://www.data.5helpyou.com/article211.html

    点赞 评论 复制链接分享
  • qq962464qq 紫轩崖 5年前

    你直接把表的数据给粘出来,这样不好理解

    点赞 评论 复制链接分享
  • seeze Zedee 5年前

    说的有点抽象,能否有具体的表结构及查询条件

    点赞 评论 复制链接分享