SQL一次性查詢一個欄位不同條件下的統計結果

參考了一下這篇文章：https://blog.csdn.net/xichenguan/article/details/51764100 ， 感謝原作者

有兩個表，分別存放了【操作員】和【單據】，要根據單據的不同類型來分類彙總(銷售單、銷售退貨單，筆數和金額)，並且顯示在同一張表裡，不想用做兩次查詢再合併的方法，研究了一下，終於搞定：

d_employee表

d_bilndx表

代碼如下：

select  b.inputid as 開單員編號, 
        e.fullname as 開單員, 

        isnull( ( select count(*)
          from d_bilndx
          where draft=3 and biltype=12 and d_bilndx.inputid=e.id
        ), 0) as '銷售開單筆數',

        isnull( ( select sum(d_bilndx.amount)
          from d_bilndx
          where draft=3 and biltype=12 and d_bilndx.inputid=e.id
        ), 0) as '銷售開單金額',

        isnull( ( select count(*)
          from d_bilndx
          where draft=3 and biltype=13 and d_bilndx.inputid=e.id
        ), 0) as '銷售退單筆數',

        isnull( ( select sum(d_bilndx.amount)
          from d_bilndx
          where draft=3 and biltype=13 and d_bilndx.inputid=e.id
        ), 0) as '銷售退單金額',

        count(b.biltype) as 開單總筆數,
        sum(b.Amount) as 開單金額

from d_bilndx as b
left join d_employee as e 
on b.inputid=e.id 
where b.draft=3 and ( b.biltype=12 or b.biltype=13 )
group by b.inputid, e.fullname, e.id

得到結果：

補記：以上代碼有一個問題，就是如果沒有符合條件的單據，查到的結果為空，而我們可能希望，查不到符合條件的記錄，相關欄位要顯示為0，改寫代碼如下：

select  e1.id as ePersonCode, e1.FullName as eFullName, 
        isnull(Bill_Sale_NUm, 0) as Bill_Sale_Num, 
        isnull(Bill_Sale_Amount, 0) as Bill_Sale_Amount, 
        isnull(Bill_SaleReturn_Num, 0) as Bill_SaleReturn_Num, 
        isnull(Bill_SaleReturn_Amount, 0) as Bill_SaleReturn_Amount
from d_employee as e1
left join (

        select  b.inputid as ePersonCode, 
                e.fullname as eFullName, 
        
                isnull( ( select count(*)
                  from d_bilndx
                  where draft=3 and biltype=12 and d_bilndx.inputid=e.id
                ), 0) as Bill_Sale_Num,
        
                isnull( ( select sum(d_bilndx.amount)
                  from d_bilndx
                  where draft=3 and biltype=12 and d_bilndx.inputid=e.id
                ), 0) as Bill_Sale_Amount,
        
                isnull( ( select count(*)
                  from d_bilndx
                  where draft=3 and biltype=13 and d_bilndx.inputid=e.id
                ), 0) as Bill_SaleReturn_Num,
        
                isnull( ( select sum(d_bilndx.amount)
                  from d_bilndx
                  where draft=3 and biltype=13 and d_bilndx.inputid=e.id
                ), 0) as Bill_SaleReturn_Amount,
        
                count(b.biltype) as Bill_Total_Num

        from d_employee as e 
        left join d_bilndx as b
        on b.inputid=e.id 
        where (b.draft=3 or b.draft=2) and ( b.biltype=12 or b.biltype=13 ) and ( b.date>='2018-05-31' and b.date<='2018-05-31' )
        group by b.inputid, e.fullname, e.id


) as t1
on e1.id = t1.ePersonCode
where e1.id<>'00000'

得到結果如下：