如何求出连续三天销货量大于1000的日期
--如何求出连续三天销货量大于1000的日期
--产品销货明细表(货品编号,销售日期,销售数量,销货单号)
create table t_trans(id int identity(1,1),item_no varchar(30),s_date datetime,item_qty numeric(20,4),bill varchar(40))
--如何求出连续三天销货量大于1000的日期,先按日期汇总销售数量,再判断是否连续三天销货量大于1000,
--如是则显示明细,最好也显示出是第几天连续,
insert into t_trans(item_no,s_date,item_qty,bill)
select 'A01','2006-03-02',1000,'a0000001'
union all
select 'A01','2006-03-03',1000,'a0000002'
union all
select 'A01','2006-03-04',800,'a0000011'
union all
select 'A01','2006-03-04',300,'a0000121'
union all
select 'A01','2006-03-05',200,'a00004441'
union all
select 'A01','2006-03-06',1500,'a00004451'
union all
select 'A01','2006-03-07',1600,'a00444522'
union all
select 'A01','2006-03-08',600,'a00444532'
union all
select 'A01','2006-03-08',700,'a00444552'
union all
select 'A01','2006-03-09',700,'a00444552'
--如上示例数据,结果集如下,6号及9号的记录不能选取
/*
1 A01 2006-03-02 00:00:00.000 1000.0000 a0000001
2 A01 2006-03-03 00:00:00.000 1000.0000 a0000002
3 A01 2006-03-04 00:00:00.000 800.0000 a0000011
4 A01 2006-03-04 00:00:00.000 300.0000 a0000121
6 A01 2006-03-06 00:00:00.000 1500.0000 a00004451
7 A01 2006-03-07 00:00:00.000 1600.0000 a00444522
8 A01 2006-03-08 00:00:00.000 600.0000 a00444532
9 A01 2006-03-08 00:00:00.000 700.0000 a00444552
*/