有一个分成表 yjinfo
ID（int） uid(varchar) fcxm1(money) fcxm2(money) fcxm3(money) ffrq(datetime)
1 1 3.0 2.2 2.23 2012-3-1
2 1 3.1 1.2 2.13 2012-3-3
3 2 3.1 1.2 2.13 2012-3-6
4 3 3.1 1.2 2.13 2012-4-5
5 3 3.1 1.2 2.13 2012-3-12
6 1 1.2 4.56 1.11 2012-3-14
最终要的结果是按月分项总和及月度个人之和
uid(varchar) ffrq(datetime)) fcxm1(money) fcxm2(money) fcxm3(money) zh(money)
1 2012-3 * * * *
2 2012-3 * * * *
3 2012-3 * * * *
1 2012-4 * * * *
2 2012-4 * * * *
3 2012-4 * * * *
------解决方案--------------------
- SQL code
IF OBJECT_ID('[yjinfo]') IS NOT NULL DROP TABLE [yjinfo]GO CREATE TABLE [yjinfo]([ID] INT,[uid] varchar,[fcxm1] money,[fcxm2] money,[fcxm3] money,[ffrq] datetime)insert into [yjinfo]select 1,'1', 3.0, 2.2, 2.23,'2012-3-1' union all select 2,'1', 3.1, 1.2, 2.13,'2012-3-3' union all select 3,'2', 3.1, 1.2, 2.13,'2012-3-6' union all select 4,'3', 3.1, 1.2, 2.13,'2012-4-5' union all select 5,'3', 3.1,1.2, 2.13,'2012-3-12' union allselect 6,'1', 1.2, 4.56, 1.11,'2012-3-14'select [uid], ffrq=convert(varchar(7),[ffrq],120),fcxm1=sum([fcxm1]),fcxm2=sum([fcxm2]),fcxm3=sum([fcxm3]),zh=sum([fcxm1])+sum([fcxm2])+sum([fcxm3]) from [yjinfo]group by convert(varchar(7),[ffrq],120),[uid]order by [uid] asc/*(6 row(s) affected)uid ffrq fcxm1 fcxm2 fcxm3 zh---- ------- --------------------- --------------------- --------------------- ---------------------1 2012-03 7.30 7.96 5.47 20.732 2012-03 3.10 1.20 2.13 6.433 2012-03 3.10 1.20 2.13 6.433 2012-04 3.10 1.20 2.13 6.43(4 row(s) affected)*/
------解决方案--------------------
- SQL code
SELECT [uid], ffrq = CONVERT(VARCHAR(7), [ffrq], 120), fcxm1 = SUM([fcxm1]), fcxm2 = SUM([fcxm2]), fcxm3 = SUM([fcxm3]), zh = SUM([fcxm1]) + SUM([fcxm2]) + SUM([fcxm3])FROM [yjinfo]GROUP BY CONVERT(VARCHAR(7), [ffrq], 120), [uid]ORDER BY [uid] ASC