问：说有张表 Table (Date, Name, Income).
我想按Name和Date进行月份分组合计Income(不同的姓名和月份要区分出来)，并且增加一个字段Cum，显示各月的Income的累加(当然月份是按升序排列)。Date字段是日期型，格式:mm/dd/yyyy,这个查询怎么写?谢谢!
答：如果没猜错的话你的原意，Income
和Cum是两个字段，结果集应该是:
Month Name Income/Month Cum
Cum应该显示逐月累加的值，Income/Month显示的是各月的小计值。
Declare @t
Table(Date datetime,name varchar(50),Income int)
Insert @t Select '2006-5-9','A',200
Union all Select '2006-5-19','B',300
Union all Select '2006-6-19','A',300
Union all Select '2006-6-29','B',300
--group by convert(varchar(7) , date , 121)
/*
Select sum(Income) ,convert(varchar(7) , Date , 121) , Name
from @t group by name, convert(varchar(7) , Date
, 121) with rollup
order by convert(varchar(7) , Date , 121)
*/
SELECT convert(varchar(7) ,
Date , 121) as date1,
-- CASE WHEN (GROUPING(
convert(varchar(7) ,Date,121) ) = 1) THEN 'ALL'
--
ELSE convert(varchar(7) , Date
, 121) --ISNULL(convert(varchar(7) , Date , 121), 'UNKNOWN')
--WHEN (GROUPING( convert(varchar(7) ,Date,121) ) <> 1) THEN
convert(varchar(7) ,Date,121)
--
END AS Date2,
CASE WHEN (GROUPING(name) = 1) THEN 'ALL'
ELSE ISNULL(name, 'UNKNOWN')
END AS Name,
SUM(Income) AS QtySum
FROM @t
--where
--date1 is not null
GROUP BY convert(varchar(7) , Date , 121), name WITH ROLLUP
order by convert(varchar(7) , Date , 121) asc
-------
/*
Month
Name
Income/Month
CUM
------ --------------------------------------------------
------------ -----------
200605
A
200
200
200606
A
300
500
200605
B
300
300
200606
B
300
600
*/