日期:2014-05-18 浏览次数:20584 次
--> 测试数据:[calendar] if object_id('[calendar]') is not null drop table [calendar] create table [calendar]([date] datetime,[type] int) insert [calendar] select '2012-01-01',1 union all select '2012-01-02',1 union all select '2012-01-03',1 union all select '2012-01-04',2 union all select '2012-01-05',3 union all select '2012-12-27',2 union all select '2012-12-28',1 union all select '2012-12-29',1 union all select '2012-12-30',1 union all select '2012-12-31',3 select case when day([date])<=25 then CONVERT(varchar(7),dateadd(mm,-1,[date]),120) else CONVERT(varchar(7),[date],120) end as 月份, SUM(case when [type]=1 then 6.74 else 0 end) as 当月工作小时数 from [calendar] group by case when day([date])<=25 then CONVERT(varchar(7),dateadd(mm,-1,[date]),120) else CONVERT(varchar(7),[date],120) end /* 月份 当月工作小时数 2011-12 20.22 2012-12 20.22 */ 反正就是这么一个思路
------解决方案--------------------
--楼上借用一下数据
if object_id('[calendar]') is not null drop table [calendar]
create table [calendar]([date] datetime,[type] int)
insert [calendar]
select '2012-01-01',1 union all
select '2012-01-02',1 union all
select '2012-01-03',1 union all
select '2012-01-04',2 union all
select '2012-01-05',3 union all
select '2012-12-27',2 union all
select '2012-12-28',1 union all
select '2012-12-29',1 union all
select '2012-12-30',1 union all
select '2012-12-31',3
select convert(nvarchar(7),A.date,23),
(select sum(case [type] when 1 then 6.74 else 0 end) from [calendar] B where convert(nvarchar(7),A.date,23)
=convert(nvarchar(7),[date],23))
from [calendar] A group by convert(nvarchar(7),[date],23)