34,576
社区成员
发帖
与我相关
我的任务
分享如何求出连续三天销货量大于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
*/
--产品销货明细表(货品编号,销售日期,销售数量,销货单号)
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
*/
...全文
请发表友善的回复…
发表回复
DengXingJie 2006-04-07
- 打赏
- 举报
select a.* from
(select * ,dateadd("d",1,s_date) as s_date1,dateadd("d",2,s_date) as s_date2 from (select s_date,sum(item_qty) as item_qty from t_trans group by s_date having sum(item_qty)>=1000) m) a
join (select * ,dateadd("d",1,s_date) as s_date1,dateadd("d",2,s_date) as s_date2 from (select s_date,sum(item_qty) as item_qty from t_trans group by s_date having sum(item_qty)>=1000) m) b on a.s_date1=b.s_date
join (select * ,dateadd("d",1,s_date) as s_date1,dateadd("d",2,s_date) as s_date2 from (select s_date,sum(item_qty) as item_qty from t_trans group by s_date having sum(item_qty)>=1000) m) c on a.s_date2=c.s_date
union
select b.* from
(select * ,dateadd("d",1,s_date) as s_date1,dateadd("d",2,s_date) as s_date2 from (select s_date,sum(item_qty) as item_qty from t_trans group by s_date having sum(item_qty)>=1000) m) a
join (select * ,dateadd("d",1,s_date) as s_date1,dateadd("d",2,s_date) as s_date2 from (select s_date,sum(item_qty) as item_qty from t_trans group by s_date having sum(item_qty)>=1000) m) b on a.s_date1=b.s_date
join (select * ,dateadd("d",1,s_date) as s_date1,dateadd("d",2,s_date) as s_date2 from (select s_date,sum(item_qty) as item_qty from t_trans group by s_date having sum(item_qty)>=1000) m) c on a.s_date2=c.s_date
union
select c.* from
(select * ,dateadd("d",1,s_date) as s_date1,dateadd("d",2,s_date) as s_date2 from (select s_date,sum(item_qty) as item_qty from t_trans group by s_date having sum(item_qty)>=1000) m) a
join (select * ,dateadd("d",1,s_date) as s_date1,dateadd("d",2,s_date) as s_date2 from (select s_date,sum(item_qty) as item_qty from t_trans group by s_date having sum(item_qty)>=1000) m) b on a.s_date1=b.s_date
join (select * ,dateadd("d",1,s_date) as s_date1,dateadd("d",2,s_date) as s_date2 from (select s_date,sum(item_qty) as item_qty from t_trans group by s_date having sum(item_qty)>=1000) m) c on a.s_date2=c.s_date
試試這條語句，連續三天以上都能顯示出來
(select * ,dateadd("d",1,s_date) as s_date1,dateadd("d",2,s_date) as s_date2 from (select s_date,sum(item_qty) as item_qty from t_trans group by s_date having sum(item_qty)>=1000) m) a
join (select * ,dateadd("d",1,s_date) as s_date1,dateadd("d",2,s_date) as s_date2 from (select s_date,sum(item_qty) as item_qty from t_trans group by s_date having sum(item_qty)>=1000) m) b on a.s_date1=b.s_date
join (select * ,dateadd("d",1,s_date) as s_date1,dateadd("d",2,s_date) as s_date2 from (select s_date,sum(item_qty) as item_qty from t_trans group by s_date having sum(item_qty)>=1000) m) c on a.s_date2=c.s_date
union
select b.* from
(select * ,dateadd("d",1,s_date) as s_date1,dateadd("d",2,s_date) as s_date2 from (select s_date,sum(item_qty) as item_qty from t_trans group by s_date having sum(item_qty)>=1000) m) a
join (select * ,dateadd("d",1,s_date) as s_date1,dateadd("d",2,s_date) as s_date2 from (select s_date,sum(item_qty) as item_qty from t_trans group by s_date having sum(item_qty)>=1000) m) b on a.s_date1=b.s_date
join (select * ,dateadd("d",1,s_date) as s_date1,dateadd("d",2,s_date) as s_date2 from (select s_date,sum(item_qty) as item_qty from t_trans group by s_date having sum(item_qty)>=1000) m) c on a.s_date2=c.s_date
union
select c.* from
(select * ,dateadd("d",1,s_date) as s_date1,dateadd("d",2,s_date) as s_date2 from (select s_date,sum(item_qty) as item_qty from t_trans group by s_date having sum(item_qty)>=1000) m) a
join (select * ,dateadd("d",1,s_date) as s_date1,dateadd("d",2,s_date) as s_date2 from (select s_date,sum(item_qty) as item_qty from t_trans group by s_date having sum(item_qty)>=1000) m) b on a.s_date1=b.s_date
join (select * ,dateadd("d",1,s_date) as s_date1,dateadd("d",2,s_date) as s_date2 from (select s_date,sum(item_qty) as item_qty from t_trans group by s_date having sum(item_qty)>=1000) m) c on a.s_date2=c.s_date
試試這條語句，連續三天以上都能顯示出來
stone66789 2006-04-06
- 打赏
- 举报
不符合要求,要求出连续三天销货量大于1000的日期,先按日期汇总销售数量,再判断是否连续三天销货量大于1000,不是只找日销货理大于1000的记录
-狙击手- 2006-04-05
- 打赏
- 举报
create table t_trans(id int identity(1,1),item_no varchar(30),s_date datetime,item_qty numeric(20,4),bill varchar(40))
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'
go
select * from t_trans where s_date in(
select s_date from t_trans
--where item_qty> 1000
group by s_date
having sum(item_qty) >= 1000)
drop table t_trans
/*
id item_no s_date item_qty bill
----------- ------------ ------------------------- ---------------------- ----------------------------------------
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
（所影响的行数为 8 行）
*/
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'
go
select * from t_trans where s_date in(
select s_date from t_trans
--where item_qty> 1000
group by s_date
having sum(item_qty) >= 1000)
drop table t_trans
/*
id item_no s_date item_qty bill
----------- ------------ ------------------------- ---------------------- ----------------------------------------
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
（所影响的行数为 8 行）
*/
-狙击手- 2006-04-05
- 打赏
- 举报
id item_no s_date item_qty bill
----------- ------------------------------ ------------------------------------------------------ ---------------------- ----------------------------------------
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
-狙击手- 2006-04-05
- 打赏
- 举报
create table t_trans(id int identity(1,1),item_no varchar(30),s_date datetime,item_qty numeric(20,4),bill varchar(40))
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'
go
select * from t_trans where s_date in(
select s_date as item_qty from t_trans
--where item_qty> 1000
group by s_date
having sum(item_qty) >= 1000)
drop table t_trans
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'
go
select * from t_trans where s_date in(
select s_date as item_qty from t_trans
--where item_qty> 1000
group by s_date
having sum(item_qty) >= 1000)
drop table t_trans
stone66789 2006-04-05
- 打赏
- 举报
TOP 1
DengXingJie 2006-04-04
- 打赏
- 举报
首先得問清一點
所謂的連續是按正常的日期還是按實際有發生銷售的日期
比如：2006/04/07、2006/04/08、2006/04/09、2006/04/10
2006/04/09這一天是休息日不上班。
而2006/04/07、2006/04/08、2006/04/10三天的銷售量都大於1000
這樣是否算連續?
所謂的連續是按正常的日期還是按實際有發生銷售的日期
比如：2006/04/07、2006/04/08、2006/04/09、2006/04/10
2006/04/09這一天是休息日不上班。
而2006/04/07、2006/04/08、2006/04/10三天的銷售量都大於1000
這樣是否算連續?
sxdoujg 2006-04-04
- 打赏
- 举报
学习1
stone66789 2006-04-04
- 打赏
- 举报
TOP
stone66789 2006-04-04
- 打赏
- 举报
to DengXingJie
不用考虑节假日,只是自然日期连续即可
不用考虑节假日,只是自然日期连续即可
fzc20031210 2006-04-04
- 打赏
- 举报
up
$扫地僧$ 2006-04-03
- 打赏
- 举报
create table t_trans(id int identity(1,1),item_no varchar(30),s_date datetime,item_qty numeric(20,4),bill varchar(40))
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'
select * from t_trans T
where exists(select 1 from (select item_no,s_date,sum(item_qty) as item_qty from t_trans group by item_no,s_date) A where datediff(dd,s_date,dateadd(dd,-1,T.s_date))=0 and item_qty>1000)
and exists(select 1 from (select item_no,s_date,sum(item_qty) as item_qty from t_trans group by item_no,s_date) A where datediff(dd,s_date,dateadd(dd,1,T.s_date))=0 and item_qty>1000)
and exists(select 1 from (select item_no,s_date,sum(item_qty) as item_qty from t_trans group by item_no,s_date) A where datediff(dd,s_date,dateadd(dd,0,T.s_date))=0 and item_qty>1000)
union
select * from t_trans T
where exists(select 1 from (select item_no,s_date,sum(item_qty) as item_qty from t_trans group by item_no,s_date) A where datediff(dd,s_date,dateadd(dd,-1,T.s_date))=0 and item_qty>1000)
and exists(select 1 from (select item_no,s_date,sum(item_qty) as item_qty from t_trans group by item_no,s_date) A where datediff(dd,s_date,dateadd(dd,-2,T.s_date))=0 and item_qty>1000)
and exists(select 1 from (select item_no,s_date,sum(item_qty) as item_qty from t_trans group by item_no,s_date) A where datediff(dd,s_date,dateadd(dd,0,T.s_date))=0 and item_qty>1000)
union
select * from t_trans T
where exists(select 1 from (select item_no,s_date,sum(item_qty) as item_qty from t_trans group by item_no,s_date) A where datediff(dd,s_date,dateadd(dd,1,T.s_date))=0 and item_qty>1000)
and exists(select 1 from (select item_no,s_date,sum(item_qty) as item_qty from t_trans group by item_no,s_date) A where datediff(dd,s_date,dateadd(dd,2,T.s_date))=0 and item_qty>1000)
and exists(select 1 from (select item_no,s_date,sum(item_qty) as item_qty from t_trans group by item_no,s_date) A where datediff(dd,s_date,dateadd(dd,0,T.s_date))=0 and item_qty>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'
select * from t_trans T
where exists(select 1 from (select item_no,s_date,sum(item_qty) as item_qty from t_trans group by item_no,s_date) A where datediff(dd,s_date,dateadd(dd,-1,T.s_date))=0 and item_qty>1000)
and exists(select 1 from (select item_no,s_date,sum(item_qty) as item_qty from t_trans group by item_no,s_date) A where datediff(dd,s_date,dateadd(dd,1,T.s_date))=0 and item_qty>1000)
and exists(select 1 from (select item_no,s_date,sum(item_qty) as item_qty from t_trans group by item_no,s_date) A where datediff(dd,s_date,dateadd(dd,0,T.s_date))=0 and item_qty>1000)
union
select * from t_trans T
where exists(select 1 from (select item_no,s_date,sum(item_qty) as item_qty from t_trans group by item_no,s_date) A where datediff(dd,s_date,dateadd(dd,-1,T.s_date))=0 and item_qty>1000)
and exists(select 1 from (select item_no,s_date,sum(item_qty) as item_qty from t_trans group by item_no,s_date) A where datediff(dd,s_date,dateadd(dd,-2,T.s_date))=0 and item_qty>1000)
and exists(select 1 from (select item_no,s_date,sum(item_qty) as item_qty from t_trans group by item_no,s_date) A where datediff(dd,s_date,dateadd(dd,0,T.s_date))=0 and item_qty>1000)
union
select * from t_trans T
where exists(select 1 from (select item_no,s_date,sum(item_qty) as item_qty from t_trans group by item_no,s_date) A where datediff(dd,s_date,dateadd(dd,1,T.s_date))=0 and item_qty>1000)
and exists(select 1 from (select item_no,s_date,sum(item_qty) as item_qty from t_trans group by item_no,s_date) A where datediff(dd,s_date,dateadd(dd,2,T.s_date))=0 and item_qty>1000)
and exists(select 1 from (select item_no,s_date,sum(item_qty) as item_qty from t_trans group by item_no,s_date) A where datediff(dd,s_date,dateadd(dd,0,T.s_date))=0 and item_qty>1000)
