select year(min(date)) as 年,month(min(date)) as 月 ,(select sum([money]) from Tab1 where datediff(month,date,a.date) = 0 and unit = 'SC') as SC ,(select sum([money]) from Tab1 where datediff(month,date,a.date) = 0 and unit = 'CQ') as CQ from Tab1 a group by datediff(month,0,date)
select year(min(date)) as 年,month(min(date)) as 月 ,(select sum([money]) from Tab1 where datediff(month,date,a.date) = 0 and unit = 'SC') as SC ,(select sum([money]) from Tab1 where datediff(month,date,a.date) = 0 and unit = 'CQ') as CQ from Tab1 a group by datediff(month,0,date) select year(min(date)) as 年,month(min(date)) as 月 ,sum(case when unit = 'SC' then [money] else 0 end) as sc ,sum(case when unit = 'CQ' then [money] else 0 end) as cq from Tab1 group by datediff(month,0,date)
select min(dateadd(month,N.i,dateadd(year,datediff(year,0,Y.d),0))) ,sum(case when a.unit = 'SC' then a.[money] else 0 end) as sc ,sum(case when a.unit = 'CQ' then a.[money] else 0 end) as cq from ( select 0 as i union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9 union all select 10 union all select 11 ) Nleft join(select min(date) as d from tab1 group by datediff(year,0,date)) Yon datediff(year,dateadd(month,N.i,dateadd(year,datediff(year,0,Y.d),0)),Y.d)=0left joinTab1 a on datediff(month,a.date, dateadd(month,N.i,dateadd(year,datediff(year,0,d),0))) = 0 group by datediff(month,0,dateadd(month,N.i,dateadd(year,datediff(year,0,Y.d),0)))
select cast(year(min(dateadd(month,N.i,dateadd(year,datediff(year,0,Y.d),0)))) as varchar) + '-' + cast(month(min(dateadd(month,N.i,dateadd(year,datediff(year,0,Y.d),0)))) as varchar) ,sum(case when a.unit = 'SC' then a.[money] else 0 end) as sc ,sum(case when a.unit = 'CQ' then a.[money] else 0 end) as cq from ( select 0 as i union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9 union all select 10 union all select 11 ) Nleft join(select min(date) as d from tab1 group by datediff(year,0,date)) Yon datediff(year,dateadd(month,N.i,dateadd(year,datediff(year,0,Y.d),0)),Y.d)=0left joinTab1 a on datediff(month,a.date, dateadd(month,N.i,dateadd(year,datediff(year,0,d),0))) = 0 group by datediff(month,0,dateadd(month,N.i,dateadd(year,datediff(year,0,Y.d),0))) declare @ varchar(8000) ,@1 varchar(8000) ,@2 varchar(8000) ,@3 varchar(8000) ,@4 varchar(8000) ,@5 varchar(8000)set @ = '' set @1 = '' set @2 = '' set @3 = '' set @4 = '' set @5 = ''select @ = @ + ',sum(case when a.unit = ''' + unit + ''' then a.[money] else 0 end) as ' + unit from Tab1 group by unit select @1 = 'select cast(year(min(dateadd(month,N.i,dateadd(year,datediff(year,0,Y.d),0)))) as varchar) + ''-'' + cast(month(min(dateadd(month,N.i,dateadd(year,datediff(year,0,Y.d),0)))) as varchar) as 年月' ,@2 = ' from (select 0 as i union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9 union all select 10 union all select 11) N' ,@3 = ' left join (select min(date) as d from tab1 group by datediff(year,0,date)) Y on datediff(year,dateadd(month,N.i,dateadd(year,datediff(year,0,Y.d),0)),Y.d)=0' ,@4 = ' left join Tab1 a on datediff(month,a.date, dateadd(month,N.i,dateadd(year,datediff(year,0,d),0))) = 0' ,@5 = ' group by datediff(month,0,dateadd(month,N.i,dateadd(year,datediff(year,0,Y.d),0)))'
exec (@1 + @ + @2 + @3 + @4 + @5)
select cast(datepart(yy,date) as char(4))+'-'+cast(datepart(mm,date) as varchar(2)) as dt ,sum((case unit when 'sc' then money else 0 end)) as sc ,sum((case unit when 'cq' then money else 0 end)) as cq from tab1 group by datepart(yy,date),datepart(mm,date)
,(select sum([money])
from Tab1
where datediff(month,date,a.date) = 0 and unit = 'SC') as SC
,(select sum([money])
from Tab1
where datediff(month,date,a.date) = 0 and unit = 'CQ') as CQ
from Tab1 a
group by datediff(month,0,date)
,(select sum([money])
from Tab1
where datediff(month,date,a.date) = 0 and unit = 'SC') as SC
,(select sum([money])
from Tab1
where datediff(month,date,a.date) = 0 and unit = 'CQ') as CQ
from Tab1 a
group by datediff(month,0,date)
select year(min(date)) as 年,month(min(date)) as 月
,sum(case when unit = 'SC' then [money] else 0 end) as sc
,sum(case when unit = 'CQ' then [money] else 0 end) as cq
from Tab1
group by datediff(month,0,date)
,sum(case when a.unit = 'SC' then a.[money] else 0 end) as sc
,sum(case when a.unit = 'CQ' then a.[money] else 0 end) as cq
from
(
select 0 as i
union all
select 1
union all
select 2
union all
select 3
union all
select 4
union all
select 5
union all
select 6
union all
select 7
union all
select 8
union all
select 9
union all
select 10
union all
select 11
) Nleft join(select min(date) as d
from tab1
group by datediff(year,0,date)) Yon datediff(year,dateadd(month,N.i,dateadd(year,datediff(year,0,Y.d),0)),Y.d)=0left joinTab1 a on datediff(month,a.date, dateadd(month,N.i,dateadd(year,datediff(year,0,d),0))) = 0 group by datediff(month,0,dateadd(month,N.i,dateadd(year,datediff(year,0,Y.d),0)))
+ '-' + cast(month(min(dateadd(month,N.i,dateadd(year,datediff(year,0,Y.d),0)))) as varchar)
,sum(case when a.unit = 'SC' then a.[money] else 0 end) as sc
,sum(case when a.unit = 'CQ' then a.[money] else 0 end) as cq
from
(
select 0 as i
union all
select 1
union all
select 2
union all
select 3
union all
select 4
union all
select 5
union all
select 6
union all
select 7
union all
select 8
union all
select 9
union all
select 10
union all
select 11
) Nleft join(select min(date) as d
from tab1
group by datediff(year,0,date)) Yon datediff(year,dateadd(month,N.i,dateadd(year,datediff(year,0,Y.d),0)),Y.d)=0left joinTab1 a on datediff(month,a.date, dateadd(month,N.i,dateadd(year,datediff(year,0,d),0))) = 0 group by datediff(month,0,dateadd(month,N.i,dateadd(year,datediff(year,0,Y.d),0)))
declare @ varchar(8000)
,@1 varchar(8000)
,@2 varchar(8000)
,@3 varchar(8000)
,@4 varchar(8000)
,@5 varchar(8000)set @ = ''
set @1 = ''
set @2 = ''
set @3 = ''
set @4 = ''
set @5 = ''select @ = @
+
',sum(case when a.unit = '''
+ unit + ''' then a.[money] else 0 end) as ' + unit
from Tab1
group by unit
select @1 = 'select cast(year(min(dateadd(month,N.i,dateadd(year,datediff(year,0,Y.d),0)))) as varchar) + ''-'' + cast(month(min(dateadd(month,N.i,dateadd(year,datediff(year,0,Y.d),0)))) as varchar) as 年月'
,@2 = ' from (select 0 as i union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9 union all select 10 union all select 11) N'
,@3 = ' left join (select min(date) as d from tab1 group by datediff(year,0,date)) Y on datediff(year,dateadd(month,N.i,dateadd(year,datediff(year,0,Y.d),0)),Y.d)=0'
,@4 = ' left join Tab1 a on datediff(month,a.date, dateadd(month,N.i,dateadd(year,datediff(year,0,d),0))) = 0'
,@5 = ' group by datediff(month,0,dateadd(month,N.i,dateadd(year,datediff(year,0,Y.d),0)))'
exec (@1 + @ + @2 + @3 + @4 + @5)
,sum((case unit when 'sc' then money else 0 end)) as sc
,sum((case unit when 'cq' then money else 0 end)) as cq
from tab1
group by datepart(yy,date),datepart(mm,date)