22,210
社区成员
发帖
与我相关
我的任务
分享
--构造测试数据
create table #tb
(id int, 姓名 nvarchar(10), 日期 date, 合格条数 int, 有问题条数 int)
insert into #tb
select 1, '陈红', '2012-9-1', 539, 0 union all select
2, '陈红', '2012-9-2', 523, 4 union all select
3, '陈红', '2012-9-3', 1080, 5 union all select
4, '陈红', '2012-9-4', 570, 0 union all select
5, '陈红', '2012-9-5', 514, 0 union all select
6, '陈红', '2012-9-6', 574, 1 union all select
7, '陈红', '2012-9-7', 545, 12 union all select
8, '陈红', '2012-9-8', 556, 0 union all select
9, '陈红', '2012-9-9', 597, 0 union all select
10, '陈红', '2012-9-10', 624, 0 union all select
1, '陈杰新', '2012-9-1', 445, 3 union all select
2, '陈杰新', '2012-9-2', 436, 0 union all select
3, '陈杰新', '2012-9-3', 443, 0 union all select
4, '陈杰新', '2012-9-4', 314, 0 union all select
5, '陈杰新', '2012-9-5', 471, 3 union all select
6, '陈杰新', '2012-9-6', 447, 0 union all select
7, '陈杰新', '2012-9-7', 472, 0 union all select
8, '陈杰新', '2012-9-9', 497, 0 union all select
9, '陈杰新', '2012-9-12', 500, 2 union all select
10, '陈杰新', '2012-9-13', 475, 0 union all select
11, '陈杰新', '2012-9-14', 435, 2 union all select
1, '崔兵强', '2012-9-1', 464, 4 union all select
2, '崔兵强', '2012-9-3', 478, 0 union all select
3, '崔兵强', '2012-9-4', 520, 0 union all select
4, '崔兵强', '2012-9-5', 559, 2
--假设你的数据一个人一天只有一条记录
;with
CET0 as(select *,row_number()over(partition by 姓名 order by 日期) rn from #tb)
,CET1 AS (select *,row_number()over(partition by 姓名 order by 日期) rn1
from cet0 where 有问题条数 = 0)
,CET2 as(select *,rn - rn1 as partnum from CET1)
select 姓名,min(日期)as 开始日期,max(日期)as 结束日期, sum(合格条数) 条数
from CET2 group by 姓名, partnum having sum(合格条数) > 1100
/*姓名 开始日期 结束日期 条数
---------- ---------- ---------- -----------
陈杰新 2012-09-02 2012-09-04 1193
陈杰新 2012-09-06 2012-09-09 1416
陈红 2012-09-08 2012-09-10 1777
(3 行受影响)
*/
if OBJECT_ID('tb') is not null drop table tb
go
create table tb
(
ID int,
姓名 nvarchar(10),
生产日期 varchar(10),
合格条数 int,
缺陷条数 int
)
insert into tb
select
1, '陈红', '2012-9-1', 539, 0 union all select
2, '陈红', '2012-9-2', 523, 4 union all select
3, '陈红', '2012-9-3', 1080, 5 union all select
4, '陈红', '2012-9-4', 570, 0 union all select
5, '陈红', '2012-9-5', 514, 0 union all select
6, '陈红', '2012-9-6', 574, 1 union all select
7, '陈红', '2012-9-7', 545, 12 union all select
8, '陈红', '2012-9-8', 556, 0 union all select
9, '陈红', '2012-9-9', 597, 0 union all select
10, '陈红', '2012-9-10', 624, 0 union all select
1, '陈杰新', '2012-9-1',445, 3 union all select
2, '陈杰新', '2012-9-2', 436, 0 union all select
3, '陈杰新', '2012-9-3', 443, 0 union all select
4, '陈杰新', '2012-9-4', 314, 0 union all select
5, '陈杰新', '2012-9-5', 471, 3 union all select
6, '陈杰新', '2012-9-6', 447, 0 union all select
7, '陈杰新', '2012-9-7', 472, 0 union all select
8, '陈杰新', '2012-9-9', 497, 0 union all select
9, '陈杰新', '2012-9-12', 500, 2 union all select
10, '陈杰新', '2012-9-13', 475, 0 union all select
11, '陈杰新', '2012-9-14', 435, 2 union all select
1, '崔兵强', '2012-9-1', 464, 4 union all select
2, '崔兵强', '2012-9-3', 478, 0 union all select
3, '崔兵强', '2012-9-4', 520, 0 union all select
4, '崔兵强', '2012-9-5', 559, 2;