if not object_id(N'Tempdb..#T') is null drop table #T Go Create table #T([rq] Datetime,[qymc] nvarchar(1),[ssxq] nvarchar(2),[rsbm] nvarchar(2),[s1] decimal(18,2),[s2] decimal(18,2),[s3] decimal(18,2)) Insert #T select '2014-01-01',N'a',N'T1',N'r1',100.00,100.00,100.00 union all select '2014-01-02',N'a',N'T1',N'r1',100.00,100.00,100.00 union all select '2014-01-02',N'b',N'T2',N'r2',100.00,100.00,100.00 union all select '2014-03-02',N'a',N'T1',N'r1',100.00,100.00,100.00 GoSELECT qymc=ISNULL(a.qymc,b.qymc) ,ssxq=ISNULL(a.ssxq,b.ssxq) ,rsbm=ISNULL(a.rsbm,b.rsbm) ,a.s1 ,b.s2 FROM (Select qymc,ssxq,rsbm,SUM(s1+s2+s3) AS s1 from #T WHERE [rq]>='2014-01-01' AND [rq]<'2014-04-01' GROUP BY qymc,ssxq,rsbm) AS a FULL JOIN (Select qymc,ssxq,rsbm,SUM(s1+s2+s3) AS s2 from #T WHERE [rq]>='2014-01-01' AND [rq]<'2014-02-01' GROUP BY qymc,ssxq,rsbm) AS b ON a.qymc=b.qymc AND a.ssxq=b.ssxq AND a.rsbm=b.rsbm /* qymc ssxq rsbm s1 s2 a T1 r1 900.00 600.00 b T2 r2 300.00 300.00 */
group by 的时候加上group by convert(varchar(7),rq,21)
Select a.qymc,a.ssxq,a.rsbm,a.[1-3月],b.[1月] from ( Select qymc,ssxq,rsbm,Sum(s1+s2+s3) as [1-3月] Group by qymc,ssxq,rsbm, convert(varchar(7),rq,21)) a left join (Select qymc,ssxq,rsbm,Sum(s1+s2+s3) as [1月] Group by qymc,ssxq,rsbm, convert(varchar(7),rq,21) where convert(varchar(7),rq,21)='2014-03') b on a.qymc=b.qymc
go
--> -->
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([rq] Datetime,[qymc] nvarchar(1),[ssxq] nvarchar(2),[rsbm] nvarchar(2),[s1] decimal(18,2),[s2] decimal(18,2),[s3] decimal(18,2))
Insert #T
select '2014-01-01',N'a',N'T1',N'r1',100.00,100.00,100.00 union all
select '2014-01-02',N'a',N'T1',N'r1',100.00,100.00,100.00 union all
select '2014-01-02',N'b',N'T2',N'r2',100.00,100.00,100.00 union all
select '2014-03-02',N'a',N'T1',N'r1',100.00,100.00,100.00
GoSELECT
qymc=ISNULL(a.qymc,b.qymc)
,ssxq=ISNULL(a.ssxq,b.ssxq)
,rsbm=ISNULL(a.rsbm,b.rsbm)
,a.s1
,b.s2
FROM (Select qymc,ssxq,rsbm,SUM(s1+s2+s3) AS s1 from #T WHERE [rq]>='2014-01-01' AND [rq]<'2014-04-01' GROUP BY qymc,ssxq,rsbm) AS a
FULL JOIN (Select qymc,ssxq,rsbm,SUM(s1+s2+s3) AS s2 from #T WHERE [rq]>='2014-01-01' AND [rq]<'2014-02-01' GROUP BY qymc,ssxq,rsbm) AS b ON a.qymc=b.qymc AND a.ssxq=b.ssxq AND a.rsbm=b.rsbm
/*
qymc ssxq rsbm s1 s2
a T1 r1 900.00 600.00
b T2 r2 300.00 300.00
*/
Select a.qymc,a.ssxq,a.rsbm,a.[1-3月],b.[1月] from (
Select qymc,ssxq,rsbm,Sum(s1+s2+s3) as [1-3月]
Group by qymc,ssxq,rsbm, convert(varchar(7),rq,21)) a
left join (Select qymc,ssxq,rsbm,Sum(s1+s2+s3) as [1月]
Group by qymc,ssxq,rsbm, convert(varchar(7),rq,21)
where convert(varchar(7),rq,21)='2014-03') b on a.qymc=b.qymc