sql 时间 分组

 select Convert(varchar(7),YearMonth,23)as A ,  SUM(
    CASE WHEN Minor = '1' THEN - Convert(int,Number1) WHEN Minor = '3' and [Audit] = 2 THEN - Convert(int,Number1) WHEN Minor = '2' THEN Convert(int,Number1)  WHEN Minor = '4' THEN Convert(int,Number1)   END 
 ) 
 from T_User_Commodity 
 group by  Convert(varchar(7),YearMonth,23)

 

 

select Convert(varchar(7),PetitionTime,23)as A1,count(*) C1 from Petition
    group by  Convert(varchar(7),PetitionTime,23)

    select Convert(varchar(7),MediationTime,23)as A2,count(*) C2 from Mediation
    group by  Convert(varchar(7),MediationTime,23)


--先 A表时间和B表时间 列合并； 之后 在join
select A.月份, B.C1,C.C2 from  
( 
    select distinct Convert(varchar(7),PetitionTime,23) as 月份 from Petition union 
    select distinct Convert(varchar(7),MediationTime,23) as 月份 from Mediation 
) A 
left join (select Convert(varchar(7),PetitionTime,23)as A1,count(*) C1 from Petition
    group by  Convert(varchar(7),PetitionTime,23)) B on A.月份=B.A1
left join (
    select Convert(varchar(7),MediationTime,23)as A2,count(*) C2 from Mediation
    group by  Convert(varchar(7),MediationTime,23)) C on A.月份=C.A2
--- 查询不全; 若B表中在A表中没有出现（时间列）; 则统计不全
    select * from(
    select Convert(varchar(7),PetitionTime,23)as A1,count(*) C1 from Petition
    group by  Convert(varchar(7),PetitionTime,23)
    )A
    left join(
    select Convert(varchar(7),MediationTime,23)as A2,count(*) C2 from Mediation
    group by  Convert(varchar(7),MediationTime,23)
    )B
    on
    A.A1 = B.A2