SQL Serverはunion allを使用して複数の条件データをクエリーし、グループを結合して表示し、前年同期比の統計

    select CONVERT(char(7),a.created_yearmonth,20) created_yearmonth,
        a.countaccount countaccount,
        a.yxsl yxsl,
        a.sccdsl sccdsl,
        a.zccdsl zccdsl  
    from 
    (--  
    select         CONVERT(char(7),account.created,20) created_yearmonth,
        count(1) countaccount,
        null yxsl,
        null sccdsl,
        null zccdsl  
    from account account
    left join org_dep iddep 
    on iddep.id=account.iddep
    left join org_employee idowner 
    on idowner.id=account.idowner 
    group by        CONVERT(char(7),account.created,20)        
        union all
        --      
    select         CONVERT(char(7),account.created,20) created_yearmonth,
        null countaccount,
        count(1)  yxsl,
        null sccdsl,
        null zccdsl  
    from account account
    left join org_dep iddep 
    on iddep.id=account.iddep
    left join org_employee idowner 
    on idowner.id=account.idowner 
    where account.emshzt in( 'bad4d977a06604e2ec5621bd0285eef2') 
    group by        CONVERT(char(7),account.created,20)
        union all
        --        
    select         CONVERT(char(7),account.created,20) created_yearmonth,
        null countaccount,
        null yxsl,
        count(1) sccdsl,
        null zccdsl  
    from account account
    left join org_dep iddep 
    on iddep.id=account.iddep
    left join org_employee idowner 
    on idowner.id=account.idowner 
    where account.dbcdcs = 1
    group by        CONVERT(char(7),account.created,20)
        union all
        --        
    select         CONVERT(char(7),account.created,20) created_yearmonth,
        null countaccount,
        null yxsl,
        null sccdsl,
        count(1) zccdsl  
    from account account
    left join org_dep iddep 
    on iddep.id=account.iddep
    left join org_employee idowner 
    on idowner.id=account.idowner 
    where account.dbcdcs > 1
    group by        CONVERT(char(7),account.created,20)
    ) a
     

 
転載先:https://www.cnblogs.com/RainHouse/p/11137156.html