現在有兩個表，A表和B表，A表左連接於B表並且根據B表一個字段篩選一部分數據，此時B表被取別名為b1，得到的結果顯示在最終表的一個列中，假設這個列叫做b1。緊接著再次左連接，再根據相同字段篩選，但是這次篩選條件不同了，此時B表取別名為b2，得到的結果列假設叫b2，也顯示在最終表中，並且和之前的b1是兩個不同的列。這種情況的sql語句有沒有辦法組合起來，難道只能寫重復代碼進行兩次左連接嗎？
你描述的這種場景我也遇到過，需要對一張表進行分區間匯總，比如：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
