![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
declare @t table (Date datetime, Name varchar(30), Money money)
insert @t
select dateadd(dd,10,getdate()),'a',10.0000 union all
select dateadd(dd,11,getdate()),'a',10.0000 union all
select dateadd(dd,12,getdate()),'b',10.0000 union all
select dateadd(dd,13,getdate()),'b',10.0000 union all
select dateadd(dd,6,getdate()),'b',10.0000 union all
select dateadd(dd,-1,getdate()),'a',10.0000 union all
select dateadd(dd,-2,getdate()),'a',10.0000 union all
select dateadd(dd,-3,getdate()),'b',10.0000 union all
select dateadd(dd,-4,getdate()),'b',10.0000 union all
select dateadd(dd,-40,getdate()),'b',10.0000 union all
select dateadd(dd,-20,getdate()),'b',10.0000 union all
select dateadd(dd,30,getdate()),'b',10.0000 union all
select dateadd(dd,60,getdate()),'b',10.0000
SELECT CONVERT(VARCHAR(7),Date,120)+'-'+
case when day(date) between 1 and 15 then '上半月' else '下半月' end,
Name,
SUM(Money)
FROM @t t
GROUP BY CONVERT(VARCHAR(7),Date,120)+'-'+
case when day(date) between 1 and 15 then '上半月' else '下半月' end,
Name
insert @t
select dateadd(dd,10,getdate()),'a',10.0000 union all
select dateadd(dd,11,getdate()),'a',10.0000 union all
select dateadd(dd,12,getdate()),'b',10.0000 union all
select dateadd(dd,13,getdate()),'b',10.0000 union all
select dateadd(dd,6,getdate()),'b',10.0000 union all
select dateadd(dd,-1,getdate()),'a',10.0000 union all
select dateadd(dd,-2,getdate()),'a',10.0000 union all
select dateadd(dd,-3,getdate()),'b',10.0000 union all
select dateadd(dd,-4,getdate()),'b',10.0000 union all
select dateadd(dd,-40,getdate()),'b',10.0000 union all
select dateadd(dd,-20,getdate()),'b',10.0000 union all
select dateadd(dd,30,getdate()),'b',10.0000 union all
select dateadd(dd,60,getdate()),'b',10.0000
SELECT CONVERT(VARCHAR(7),Date,120)+'-'+
case when day(date) between 1 and 15 then '上半月' else '下半月' end,
Name,
SUM(Money)
FROM @t t
GROUP BY CONVERT(VARCHAR(7),Date,120)+'-'+
case when day(date) between 1 and 15 then '上半月' else '下半月' end,
Name
欢迎转载,转载请注明出处:http://www.cnblogs.com/Tonyyang/