有一个表table_a
rq qymc ssxq rsbm s1 s2 s3
2014-01-01 a T1 r1 100.00 100.00 100.00
2014-01-02 a T1 r1 100.00 100.00 100.00
2014-01-02 b T2 r2 100.00 100.00 100.00
2014-03-02 a T1 r1 100.00 100.00 100.00
我想得到如下结果：
qymc ssxq rsbm s1+s2+s3合计(1-3月) s1+s2+s3合计(1月)
a T1 r1 900.00 600.00
b T2 r2 300.00 300.00
因为这里s1+s2+s3是在两个不同的日期区间求和，求教如何实现啊？
万分感谢！
------解决方案--------------------
use Tempdb
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
Go
SELECT
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