一个关于sql语句的问题

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

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

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

Deep_Red
Deep_Red 对不起,因为表结构实在太复杂了,sql语句也不是这么简单,我是把问题简化了以后提问的,等一会我建一个类似的简单的例子出来吧
3 年多之前 回复

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

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

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

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

Csdn user default icon
上传中...
上传图片
插入图片
抄袭、复制答案,以达到刷声望分或其他目的的行为,在CSDN问答是严格禁止的,一经发现立刻封号。是时候展现真正的技术了!
立即提问