一个计数统计问题？
--２６天以内和２６天以外的数据区分
--目的：按员工计算其产量，同时要区分从指定日期开始前２６天和２６以后的产量//注意，日期中间可能有中断，也就是说要从指定日期开始按员工计数，看是否已满２６天
Create Table #TEST
(rq datetime,--日期
ygname varchar,--员工姓名
cl Int)--产量
Insert #TEST Select 2006-03-26, 'A ', 1
Union All Select 2006-03-27, 'A ', 1
Union All Select 2006-03-28, 'A ', 1
Union All Select 2006-03-29, 'A ', 1
Union All Select 2006-03-30, 'A ', 1
Union All Select 2006-03-31, 'A ', 1
Union All Select 2006-04-01, 'A ', 1
Union All Select 2006-04-02, 'A ', 1
Union All Select 2006-04-03, 'A ', 1
Union All Select 2006-04-04, 'A ', 1
Union All Select 2006-04-05, 'A ', 1
Union All Select 2006-04-06, 'A ', 1
Union All Select 2006-04-07, 'A ', 1
Union All Select 2006-04-08, 'A ', 1
Union All Select 2006-04-09, 'A ', 1
Union All Select 2006-04-10, 'A ', 1
Union All Select 2006-04-11, 'A ', 1
Union All Select 2006-04-12, 'A ', 1
Union All Select 2006-04-13, 'A ', 1
Union All Select 2006-04-14, 'A ', 1
Union All Select 2006-04-15, 'A ', 1
Union All Select 2006-04-16, 'A ', 1
Union All Select 2006-04-17, 'A ', 1
Union All Select 2006-04-18, 'A ', 1
Union All Select 2006-04-19, 'A ', 1
Union All Select 2006-04-20, 'A ', 1
Union All Select 2006-04-21, 'A ', 1
Union All Select 2006-04-22, 'A ', 1
Union All Select 2006-04-23, 'A ', 1
Union All Select 2006-04-24, 'A ', 1
Union All Select 2006-04-25, 'A ', 1
Union ALL Select 2006-03-27, 'B ', 1
Union ALL Select 2006-03-28, 'B ', 1
Union ALL Select 2006-03-29, 'B ', 1
Union ALL Select 2006-03-30, 'B ', 1
Union ALL Select 2006-03-31, 'B ', 1
Union ALL Select 2006-04-01, 'B ', 1
Union ALL Select 2006-04-02, 'B ', 1
Union ALL Select 2006-04-03, 'B ', 1
Union ALL Select 2006-04-04, 'B ', 1
Union ALL Select 2006-04-05, 'B ', 1
Union ALL Select 2006-04-06, 'B ', 1
Union ALL Select 2006-04-07, 'B ', 1
Union ALL Select 2006-04-08, 'B ', 1
Union ALL Select 2006-04-09, 'B ', 1
Union ALL Select 2006-04-10, 'B ', 1
Union ALL Select 2006-04-11, 'B ', 1
Union ALL Select 2006-04-12, 'B ', 1
Union ALL Select 2006-04-13, 'B ', 1
Union ALL Select 2006-04-14, 'B ', 1
Union ALL Select 2006-04-15, 'B ', 1
Union ALL Select 2006-04-16, 'B ', 1
Union ALL Select 2006-04-17, 'B ', 1
Union ALL Select 2006-04-18, 'B ', 1
Union ALL Select 2006-04-19, 'B ', 1
Union ALL Select 2006-04-20, 'B ', 1
Union ALL Select 2006-04-21, 'B ', 1
Union ALL Select 2006-04-22, 'B ', 1
Union ALL Select 2006-04-23, 'B ', 1
Union ALL Select 2006-04-24, 'B ', 1
Union ALL Select 2006-04-25, 'B ', 1
--SELECT * FROM #TEST
--结果应该如下：
YGNAME 26QH CL
A 前２６天 26
A ２６天后 5
B 前２６天 26
B２ ６天后 4
[解决办法]
seairhh(风乍起，吹皱一池秋水) ( ) 信誉：100 Blog 加为好友 2007-05-07 11:45:03 得分: 0
有重复就汇总，我看了的你是用COUNT(RQ) AS CL
--------
有重復就匯總是什麼意思？
我用的是COUNT(Distinct RQ) AS CL,每天只統計一次
難道你要的是這樣的效果？
Declare @StartRq DateTime, @EndRq DateTime
Select @StartRq = '2006-03-26 ', @EndRq = '2006-04-25 '
Select
YGNAME,
N '前２６天 ' As [26QH],
SUM(CL) As CL
From
#TEST A
Where
rq In (Select Distinct TOP 26 rq From #TEST Where YGNAME = A.YGNAME And rq Between @StartRq And @EndRq Order By rq)
Group By
YGNAME
Union
Select
YGNAME,
N '２６天后 ' As [26QH],
SUM(CL) As CL
From
#TEST A
Where
rq Not In (Select Distinct TOP 26 rq From #TEST Where YGNAME = A.YGNAME And rq Between @StartRq And @EndRq Order By rq)
Group By
YGNAME
Order By
YGNAME, [26QH] Desc
