一个关于sql语句的问题

Question

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

Answer 1

你描述的这种场景我也遇到过，需要对一张表进行分区间汇总，比如：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