高难度 sql 问题，挑战高手！

[zyhlhx]

假设有一个表，记录商品销售情况， 商品唯一编码、名称、价格、
当日销售总数量，销售日期。
现在用一个 sql 语句，找出所有商品连续3天商品销售数量低于指定数值的那些天的日期与商品。
create table sales1 (item_id numeric(8,0) not null ,
item_name varchar(40) not null,
sale_num integer not null ,
sale_date smalldatetime not null)

下表为商品报警表，存放所有商品日销售下限报警数
create table sale_alarm( item_id numeric(8,0) not null ,
alarm_num integer not null )

指定用一个sql 语句完成 销售日期为整日期格式，如 '2003-09-12'

[playyuer]

select *
,(select sale_num
from sales1
where item_id = T.item_id and datediff(day,T.sale_date,sale_date) =1)
,(select sale_num
from sales1
where item_id = T.item_id and datediff(day,T.sale_date,sale_date) =2)
from sales1 T
left join sale_alarm on T.item_id = sale_alarm.item_id
where sale_num < sale_alarm.alarm_num
and
(select sale_num
from sales1
where item_id = T.item_id and datediff(day,T.sale_date,sale_date) =1)
<sale_alarm.alarm_num
and
(select sale_num
from sales1
where item_id = T.item_id and datediff(day,T.sale_date,sale_date) =2)
< sale_alarm.alarm_num

[pengdali]

create table #sales1 (
item_id numeric(8,0) not null ,
item_name varchar(40) not null,
sale_num integer not null ,
sale_date datetime not null
)

insert #sales1 values(1001,'aa', 99,'2003-01-01')
insert #sales1 values(1001,'aa', 80,'2003-01-02')
insert #sales1 values(1001,'aa', 70,'2003-01-03')
insert #sales1 values(1001,'aa', 101,'2003-01-04')

create table #sale_alarm(item_id numeric(8,0) not null ,
alarm_num integer not null )

insert #sale_alarm values(1001,100)


--开始：

select * from #sales1 where exists(
select 1 from (select * from #sales1 where sale_num<(select alarm_num from #sale_alarm where item_id=#sales1.item_id))a
join
(select * from #sales1 where sale_num<(select alarm_num from #sale_alarm where item_id=#sales1.item_id))
b
on datediff(day,a.sale_date,b.sale_date) between 0 and 2 group by b.item_id,b.sale_date having sum(1)>2
and b.item_id=#sales1.item_id and datediff(day,#sales1.sale_date,b.sale_date) between 0 and 2 )

--结束
go

drop table #sale_alarm,#sales1

[playyuer]

select *
,(select sale_num
from sales1 a
where a.item_id = T.item_id
and a.sale_date = (select min(sale_date)
from sales1
where item_id = a.item_id
and sale_date > T.sale_date )) as 第二天量
,(select sale_num
from sales1 b
where b.item_id = T.item_id
and b.sale_date = (select min(sale_date)
from sales1 a
where a.item_id = T.item_id
and a.sale_date > (select min(sale_date)
from sales1
where item_id = a.item_id
and sale_date > T.sale_date ))) as 第三天量
from sales1 T
left join sale_alarm on T.item_id = sale_alarm.item_id
where sale_num < sale_alarm.alarm_num
and
isnull( (select sale_num
from sales1 a
where a.item_id = T.item_id
and a.sale_date = (select min(sale_date)
from sales1
where item_id = a.item_id
and sale_date > T.sale_date )),0) --第二天量
<sale_alarm.alarm_num
and
isnull( (select sale_num
from sales1 b
where b.item_id = T.item_id
and b.sale_date = (select min(sale_date)
from sales1 a
where a.item_id = T.item_id
and a.sale_date > (select min(sale_date)
from sales1
where item_id = a.item_id
and sale_date > T.sale_date ))),0) --第三天量
< sale_alarm.alarm_num

[playyuer]

--连续三"天"少于警报数:
select *
,(select sale_num
from sales1
where item_id = T.item_id and datediff(day,T.sale_date,sale_date) =1) as 第二天量
,(select sale_num
from sales1
where item_id = T.item_id and datediff(day,T.sale_date,sale_date) =2) as 第三天量
from sales1 T
left join sale_alarm on T.item_id = sale_alarm.item_id
where sale_num < sale_alarm.alarm_num
and
isnull((select sale_num
from sales1
where item_id = T.item_id and datediff(day,T.sale_date,sale_date) =1),0) --第二天量
<sale_alarm.alarm_num
and
isnull(select sale_num
from sales1
where item_id = T.item_id and datediff(day,T.sale_date,sale_date) =2),0) --第三天量
< sale_alarm.alarm_num


--==================================
--连续三条"记录"少于警报数:

select *
,(select sale_num
from sales1 a
where a.item_id = T.item_id
and a.sale_date = (select min(sale_date)
from sales1
where item_id = a.item_id
and sale_date > T.sale_date )) as 第二天量
,(select sale_num
from sales1 b
where b.item_id = T.item_id
and b.sale_date = (select min(sale_date)
from sales1 a
where a.item_id = T.item_id
and a.sale_date > (select min(sale_date)
from sales1
where item_id = a.item_id
and sale_date > T.sale_date ))) as 第三天量
from sales1 T
left join sale_alarm on T.item_id = sale_alarm.item_id
where sale_num < sale_alarm.alarm_num
and
isnull( (select sale_num
from sales1 a
where a.item_id = T.item_id
and a.sale_date = (select min(sale_date)
from sales1
where item_id = a.item_id
and sale_date > T.sale_date )),0) --第二天量
<sale_alarm.alarm_num
and
isnull( (select sale_num
from sales1 b
where b.item_id = T.item_id
and b.sale_date = (select min(sale_date)
from sales1 a
where a.item_id = T.item_id
and a.sale_date > (select min(sale_date)
from sales1
where item_id = a.item_id
and sale_date > T.sale_date ))),0) --第三天量
< sale_alarm.alarm_num

[zyhlhx]

--
create table sales1 (item_id numeric(8,0) not null ,
item_name varchar(40) not null,
sale_num integer not null ,
sale_date smalldatetime not null)

下表为商品报警表，存放所有商品日销售下限报警数
create table sale_alarm( item_id numeric(8,0) not null ,
alarm_num integer not null )
insert into sale_alarm values (1,10)
insert into sale_alarm values (2,10)
insert into sale_alarm values (3,5)
insert into sale_alarm values (4,5)

--商品销售表
insert into sales1 values (1,'毛巾',10,'2003-08-01')
insert into sales1 values (1,'毛巾',12,'2003-08-02')
insert into sales1 values (1,'毛巾',13'2003-08-03')
insert into sales1 values (1,'毛巾',14,'2003-08-04')
insert into sales1 values (1,'毛巾',9,'2003-08-05')


insert into sales1 values (2,'牙刷',10,'2003-08-01')
insert into sales1 values (2,'牙刷',12,'2003-08-02')
insert into sales1 values (2,'牙刷',8,'2003-08-03')
insert into sales1 values (2,'牙刷',6,'2003-08-04')
insert into sales1 values (2,'牙刷',3,'2003-08-05')

insert into sales1 values (3,'香皂',6,'2003-08-01')
insert into sales1 values (3,'香皂',8,'2003-08-02')
insert into sales1 values (3,'香皂',4,'2003-08-03')
insert into sales1 values (3,'香皂',9,'2003-08-04')
insert into sales1 values (3,'香皂',2,'2003-08-05')


insert into sales1 values (4,'面盆',8,'2003-08-01')
insert into sales1 values (4,'面盆',7,'2003-08-02')
insert into sales1 values (4,'面盆',4,'2003-08-03')
insert into sales1 values (4,'面盆',2,'2003-08-04')
insert into sales1 values (4,'面盆',4,'2003-08-05')

--商品报警表
--------------------------------------------------------
insert into sale_alarm values (1,10)
insert into sale_alarm values (2,10)
insert into sale_alarm values (3,5)
insert into sale_alarm values (4,5)



查询语句
查找连续3天销售数量低于报警数量的商品。

select a.* from sales1 a
where a.item_id in (
select s1.item_id from sales1 s1,sales1 s2,sales1 s3 ,sale_alarm b
where s1.item_id=s2.item_id and
s2.item_id=s3.item_id and
s3.item_id = b.item_id and
s1.sale_num<b.alarm_num and
s2.sale_num<b.alarm_num and
s3.sale_num<b.alarm_num and
s2.sale_date=dateadd(day,1,s1.sale_date)and
s3.sale_date=dateadd(day,1,s2.sale_date)
)
order by a.item_id,a.sale_date

结果

item_id item_name sale_num sale_date
---------- --------- ----------- ---------
2 牙刷 10 Aug 1 2003 12:00AM
2 牙刷 12 Aug 2 2003 12:00AM
2 牙刷 8 Aug 3 2003 12:00AM
2 牙刷 6 Aug 4 2003 12:00AM
2 牙刷 3 Aug 5 2003 12:00AM
4 面盆 8 Aug 1 2003 12:00AM
4 面盆 7 Aug 2 2003 12:00AM
4 面盆 4 Aug 3 2003 12:00AM
4 面盆 2 Aug 4 2003 12:00AM
4 面盆 4 Aug 5 2003 12:00AM

[zyhlhx]

心上人的结果：（不对）
item_id item_name sale_num sale_date item_id alarm_num 第二天量 第三天量
---------- --------- ----------- --------- ---------- ----------- ----------- -----------
1 毛巾 9 Aug 5 2003 12:00AM 1 10 NULL NULL
2 牙刷 8 Aug 3 2003 12:00AM 2 10 6 3
2 牙刷 6 Aug 4 2003 12:00AM 2 10 3 NULL
2 牙刷 3 Aug 5 2003 12:00AM 2 10 NULL NULL
3 香皂 2 Aug 5 2003 12:00AM 3 5 NULL NULL
4 面盆 4 Aug 3 2003 12:00AM 4 5 2 4
4 面盆 2 Aug 4 2003 12:00AM 4 5 4 NULL
4 面盆 4 Aug 5 2003 12:00AM 4 5 NULL NULL

[zyhlhx]

这个问题查询可以引申出去，产生很多相关问题。
不过，如果不用自连接，很难做出正确答案。谢谢大家

[falaly]

没工夫看前面的回复,,太多了...
我也给个思路吧,具体实现的脚本就不写了
step 1 : 给出三天内所有商品的销售量最大的且小于报警量的商品
SQL: Select Item_Id from sales1
where DateDiff(Day,sale_date,GetDate()) <= 3
group by Item_Id
having max(sale_num) < AlarmNum
/*****这里我用的是当前日期,所以用了GetDate函数******/
Step 2 : 由Step1得出了你所要的数据集,然后你想怎么处理都行
何况是导如报警表
不知道我有没有看错你的需求:)

[playyuer]

to zyhlhx(踏雪无痕) :
难道不是这8条记录马?

2003-1-1,1
2003-1-2,1
2003-1-3,1
2003-1-4,1
2003-1-5,1
2003-1-6,1

1,2,3 连续三天
2,3,4 连续三天
3,4,5 连续三天
4,5,6 连续三天

[zyhlhx]

在我的数据样本中，只有两种商品连续三天的销售低于报警数据

[playyuer]

to zyhlhx(踏雪无痕) 你要的:


select *
,(select sale_num
from sales1
where item_id = T.item_id and datediff(day,T.sale_date,sale_date) =1) as 第二天量
,(select sale_num
from sales1
where item_id = T.item_id and datediff(day,T.sale_date,sale_date) =2) as 第三天量
from sales1 T
left join sale_alarm on T.item_id = sale_alarm.item_id
where sale_num < sale_alarm.alarm_num
and
isnull((select sale_num
from sales1
where item_id = T.item_id and datediff(day,T.sale_date,sale_date) =1),1000) --第二天量
<sale_alarm.alarm_num
and
isnull((select sale_num
from sales1
where item_id = T.item_id and datediff(day,T.sale_date,sale_date) =2),1000) --第三天量
< sale_alarm.alarm_num

[playyuer]

--最终答案:
--实际就是去掉 isnull
--连续三"天"少于警报数:
select *
,(select sale_num
from sales1
where item_id = T.item_id and datediff(day,T.sale_date,sale_date) =1) as 第二天量
,(select sale_num
from sales1
where item_id = T.item_id and datediff(day,T.sale_date,sale_date) =2) as 第三天量
from sales1 T
left join sale_alarm on T.item_id = sale_alarm.item_id
where sale_num < sale_alarm.alarm_num
and
(select sale_num
from sales1
where item_id = T.item_id and datediff(day,T.sale_date,sale_date) =1) --第二天量
< sale_alarm.alarm_num
and
(select sale_num
from sales1
where item_id = T.item_id and datediff(day,T.sale_date,sale_date) =2) --第三天量
< sale_alarm.alarm_num





--===============================
--连续三条"记录"少于警报数:

select *
,(select sale_num
from sales1 a
where a.item_id = T.item_id
and a.sale_date = (select min(sale_date)
from sales1
where item_id = a.item_id
and sale_date > T.sale_date )) as 第二天量
,(select sale_num
from sales1 b
where b.item_id = T.item_id
and b.sale_date = (select min(sale_date)
from sales1 a
where a.item_id = T.item_id
and a.sale_date > (select min(sale_date)
from sales1
where item_id = a.item_id
and sale_date > T.sale_date ))) as 第三天量
from sales1 T
left join sale_alarm on T.item_id = sale_alarm.item_id
where sale_num < sale_alarm.alarm_num
and
(select sale_num
from sales1 a
where a.item_id = T.item_id
and a.sale_date = (select min(sale_date)
from sales1
where item_id = a.item_id
and sale_date > T.sale_date )) --第二天量
<sale_alarm.alarm_num
and
(select sale_num
from sales1 b
where b.item_id = T.item_id
and b.sale_date = (select min(sale_date)
from sales1 a
where a.item_id = T.item_id
and a.sale_date > (select min(sale_date)
from sales1
where item_id = a.item_id
and sale_date > T.sale_date ))) --第三天量
< sale_alarm.alarm_num


item_id item_name sale_num sale_date item_id alarm_num 第二天量 第三天量
---------- ---------------------------------------- ----------- ------------------------------------------------------ ---------- ----------- ----------- -----------
2 牙刷 8 2003-08-03 00:00:00 2 10 6 3
4 面盆 4 2003-08-03 00:00:00 4 5 2 4

（所影响的行数为 2 行）

item_id item_name sale_num sale_date item_id alarm_num 第二天量 第三天量
---------- ---------------------------------------- ----------- ------------------------------------------------------ ---------- ----------- ----------- -----------
2 牙刷 8 2003-08-03 00:00:00 2 10 6 3
4 面盆 4 2003-08-03 00:00:00 4 5 2 4

（所影响的行数为 2 行）

[amtyuranus]

最好用一存储过程

用一sql语句很困难，学习

[caiyunxia]

商品销售限量表了？

[CrazyFor]

select item_id,item_name,sum(sale_num) as sale_num,cast(convert(char(10),sale_date,120) as datetime) as Sale_date into #sales1 from sales1 group by item_id,item_name,convert(char(10),sale_date,120)

select distinct item_id,item_name #sales1 a left join sale_alarm b on a.item_id=b.item_id
where a.sale_num <b.alarm_num
and (select sale_num from #sales1 aa where aa.item_id=a.item_id and aa.sale_date-1=a.sale_date)
and (select sale_num from #sales1 aa where aa.item_id=a.item_id and aa.sale_date-2=a.sale_date)

[caiyunxia]

sales1 distinct item_id from sales1 where dateadd(dy,3,smalldatetime)>='2003-09-12'
and smalldatetime<'2003-09-12' and sale_num<限量

[txlicenhe]

Select itm_name,sale_date from sales1 c
where (select sum(sale_num) from sales where item_id = c.itm_id and sale_date between c.sale_date and c.sale_date+3 ) <= (select alarm_num from sale_alarm where item_id = c.item_id)

[caiyunxia]

sales1 b.item_id from sale_alarm a lsft join sales1 a on
on dateadd(dy,3,smalldatetime)>='2003-09-12' and a.item_id=b.item_id
and smalldatetime<'2003-09-12' and isnull(sale_num,0)<alarm_num
group by b.item_id
having count(*) = 3

[zjcxc]

select a.item_id,a.item_name,a.sale_date
from(
select item_id,item_name,sale_date,
sale_sum=(select sum(sale_num) from sales1 where item_id=a.item_id and datediff(day,sale_date,a.sale_date) between 0 and 2)
from sales1 a
) a inner join sale_alarm b on a.item_id=b.item_id
where a.sale_sum<b.alarm_num

[karach]

高手太多啦，我没有表现的机会