表tb1
date f1 s1 2009-10-01 a001 10 2009-10-03 a001 20 2009-10-04 a002 -202009-10-07 a001 -52009-10-09 a001 62009-10-14 a002 92009-10-25 a002 302009-10-27 a001 -122009-10-28 a001 202009-11-02 a002 82009-11-05 a001 7
结果:
year&month date f1 s12009-10 1-7 a001 252009-10 8-14 a001 62009-10 15-22 a001 02009-10 23-Last a001 82009-11 1-7 a001 72009-11 8-14 a001 02009-11 15-22 a001 02009-11 23-Last a001 02009-10 1-7 a002 -202009-10 8-14 a002 92009-10 15-22 a002 02009-10 23-Last a002 302009-11 1-7 a002 82009-11 8-14 a002 02009-11 15-22 a002 02009-11 23-Last a002 0
date f1 s1 2009-10-01 a001 10 2009-10-03 a001 20 2009-10-04 a002 -202009-10-07 a001 -52009-10-09 a001 62009-10-14 a002 92009-10-25 a002 302009-10-27 a001 -122009-10-28 a001 202009-11-02 a002 82009-11-05 a001 7
结果:
year&month date f1 s12009-10 1-7 a001 252009-10 8-14 a001 62009-10 15-22 a001 02009-10 23-Last a001 82009-11 1-7 a001 72009-11 8-14 a001 02009-11 15-22 a001 02009-11 23-Last a001 02009-10 1-7 a002 -202009-10 8-14 a002 92009-10 15-22 a002 02009-10 23-Last a002 302009-11 1-7 a002 82009-11 8-14 a002 02009-11 15-22 a002 02009-11 23-Last a002 0
# (hai)
#
# 等 级:
# 结帖率:25.00%
用convert一下,只保留年和月,然后按(day(datetimeField)-1)/7 进行分组统计就行了。
语句自己看着写吧。
-- Author : HappyFlyStone
-- Date : 2009-10-15
-- Version: Microsoft SQL Server 2005 - 9.00.2047.00 (Intel X86)
-- Apr 14 2006 01:12:25
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 2)
--
-------------------------------------------------------------------------- Test Data: ta
IF OBJECT_ID('[ta]') IS NOT NULL
DROP TABLE [ta]
Go
CREATE TABLE ta([date] SMALLDATETIME,[f1] NVARCHAR(4),[s1] INT)
Go
INSERT INTO ta
SELECT '2009-10-01','a001',10 UNION ALL
SELECT '2009-10-03','a001',20 UNION ALL
SELECT '2009-10-04','a002',-20 UNION ALL
SELECT '2009-10-07','a001',-5 UNION ALL
SELECT '2009-10-09','a001',6 UNION ALL
SELECT '2009-10-14','a002',9 UNION ALL
SELECT '2009-10-25','a002',30 UNION ALL
SELECT '2009-10-27','a001',-12 UNION ALL
SELECT '2009-10-28','a001',20 UNION ALL
SELECT '2009-11-02','a002',8 UNION ALL
SELECT '2009-11-05','a001',7
GO
--Start
select e.dt,e.t,e.[f1],isnull(sum([s1]),0) as sl
from(
SELECT
convert(char(7),[date],120) as dt,[s1] ,[f1],
case when datepart(d,[date]) between 1 and 7 then '01-07'
when datepart(d,[date]) between 8 and 14 then '08-14'
when datepart(d,[date]) between 15 and 22 then '15-22'
else '23-last' end as t
FROM
TA
) b
right join(
select dt,t,[f1]
from
(
select '01-07' as t union all select '08-15'
union all select '15-22' union all select '23-last'
) c
,
(
select convert(char(7),[date],120) as dt
from ta group by convert(char(7),[date],120)
) d
,
(
select [f1] from ta group by [f1]
) f
)e
on b.dt = e.dt and b.t = e.t and e.f1 = b.f1
group by e.dt,e.t,e.[f1]
order by 1,3
--Result:
/*
dt t f1 sl
------- ------- ---- -----------
2009-10 01-07 a001 25
2009-10 08-15 a001 0
2009-10 15-22 a001 0
2009-10 23-last a001 8
2009-10 01-07 a002 -20
2009-10 08-15 a002 0
2009-10 15-22 a002 0
2009-10 23-last a002 30
2009-11 01-07 a001 7
2009-11 08-15 a001 0
2009-11 15-22 a001 0
2009-11 23-last a001 0
2009-11 01-07 a002 8
2009-11 08-15 a002 0
2009-11 15-22 a002 0
2009-11 23-last a002 0*/
--End
,CASE (DATEPART(DAY,[date])-1)/7
WHEN 0 THEN '1-7'
WHEN 1 THEN '8-14'
WHEN 2 THEN '15-21'
ELSE '22-Last'
END as [Date]
,f1
,SUM(S1)
FROM T2
GROUP BY YEAR([date]),MONTH([date]),(DATEPART(DAY,[date])-1)/7 ,f1
ORDER BY f1,[YearMonth],(DATEPART(DAY,[date])-1)/7我的好像有点不对.为什么是22号?