Sql difficult issues, challenges, master!

Category: MS-SQL Server -> Difficult Problems Author: sdxz622 Date: 2010-04-04 03:06:02
 
sdxz622
2010-04-04 03:06:02
Suppose you have a table to record merchandise sales, merchandise only code, name, price,
the total number of sales that day, the date of sale.
now using a sql statement to identify all goods merchandise sales for 3 consecutive days of those days falls below a specified value date and merchandise.
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)

following table as a commodity alarm table, where all merchandise sold at the lower limit alarm number
create table sale_alarm (item_id numeric (8,0) not null,
alarm_num integer not null)

specify sql statement with a completion date of sale for the entire date format, such as '2003-09-12 '
Jario
2010-04-04 03:41:49
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
lian20100128b
2010-04-04 04:12:36
such as finding 2003-09-12 previous accord of records:

select distinct D. * from sales1 D,
(
select * from sales1 A
where datediff (day, '2003-09-12 ', sale_date) <= -2
and not exists
; (
select *
from sales1 ; B left join sale_alarm C on B.item_id = C.item_id
where B.sale_num> = isnull (C.alarm_num, 0)
and b.item_id = A.item_id
and datediff (day, A.sale_date, B.sale_date) between 0 and 2
;
)

) E
where D.item_id = E.item_id and datediff (day, E.sale_date, D.sale_date) between 0 and 2
order by D.item_id, D.sale_date
COSsin123
2010-04-04 04:31:44
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
spccc
2010-04-04 05:05:35
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)

- Start:

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)

- end
go

drop table # sale_alarm, # sales1
shengronglong
2010-04-04 05:25:21
best to use a stored procedure using a sql statement

very difficult to learn
a310013123
2010-04-04 06:03:56
merchandising limited form the?
hxqlxg
2010-04-04 06:26:08
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)
ycz1019
2010-04-04 07:04:30
sales1 distinct item_id from sales1 where dateadd (dy, 3, smalldatetime)> = '2003-09-12 '
and smalldatetime <'2003-09-12' and sale_num <limit
ecnit
2010-04-04 07:44:09
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)

jxp911
2010-04-04 08:02:16
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
wanghui2001
2010-04-04 08:21:01
master too many times, I do not have an opportunity to show
wyl522066863
2010-04-04 08:23:05
Thank you master your reply, I have not verified, wait verify look.
which the highest efficiency.

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.sales_num < b.alarm_num and
s2.sales_num <b.alarm_num and
s3.sales_num <b.alarm_num and
s2.sale_date = dateadd (day, 1, s1.sale_date) and
s3.sale_date = dateadd (day, 1, s2.sale_date)
)
do not know this can not?
chgxj
2010-04-04 08:33:18
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 the next volume
, (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 third volume
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) - the next volume
<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) - Day quantity
< ; sale_alarm.alarm_num
jj9809abc
2010-04-04 08:40:26
- three consecutive "days" of less than the number of alerts:
select *
, (select sale_num
from sales1
where item_id = T.item_id and ; datediff (day, T.sale_date, sale_date) = 1) as the next volume
, (select sale_num
from sales1
where item_id = T.item_id and datediff (day, T.sale_date , sale_date) = 2) as third volume
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) - Day Cover
<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) - On the third day the amount
<sale_alarm.alarm_num

- ==================================
- Continuous three "record" less than the number of alerts:

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 the first two days amount
, (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 the amount of third
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) - Second the amount of days
<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) - On the third day the amount
<sale_alarm.alarm_num
qq332776006
2010-04-04 08:55:07
-
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)

following table as a commodity alarm table, where all merchandise sold at the lower limit the number of alarms
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)

- merchandising table
insert into sales1 values ​​(1, 'towel', 10, '2003-08-01 ')
insert into sales1 values ; (1, 'towel', 12, '2003-08-02 ')
insert into sales1 values ​​(1,' towel ', 13'2003-08-03')
insert into sales1 values ​​(1, 'towel', 14, '2003-08-04 ')
insert into sales1 values ​​(1,' towel ', 9, '2003-08 -05 ')

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

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

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

- Goods alarm table
-------------------------------- ------------------------
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)

search query
sales volume for 3 consecutive days below the alarm quantity of a commodity.

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

result

item_id ; item_name ; sale_num sale_date ;
---------------------------------------
Two toothbrushes ; ; 10 Aug 1 2003 12:00 AM
2 toothbrush12 Aug 2 2003 12:00 AM
2 toothbrushes ; ; 8 Aug 3 2003 12:00 AM ;
2 toothbrushes6 Aug 4 2003 12:00 AM
2 toothbrushes ; ; 3 Aug 5 2003 12:00 AM ;
4 vanities8 Aug 1 2003 12:00 AM
4 basin ; ; 7 Aug 2 2003 ; 12:00 AM
4 ; vanities 4 Aug 3 2003 12:00 AM
4 basin ; ; 2 Aug 4 2003 12:00 AM
; 4 basin4 Aug 5 2003 12:00 AM

jsj407
2010-04-04 09:06:53
sweetheart results: (right)
item_id item_name sale_num sale_date item_id alarm_num amount
-------------- next volume on the third day ----- ; -------------------- ; ------------- ------------------------------
a towel ; ; 9 Aug 5 2003 12:00 AM ; 1 10 NULL ; NULL
2 toothbrushes8 Aug 3 2003 12:00 AM 2 106 3
2 toothbrushes ; ; 6 Aug 4 2003 12:00 AM ; 210 ; 3 NULL
; 2 toothbrushes3 Aug 5 2003 12:00 AM 2 10 NULL NULL
3 Soap ; ; 2 Aug 5 2003 12:00 AM ; 3 ; 5 NULL NULL
; 4 basin4 Aug 3 2003 12:00 AM 45 2 4
4 basin ; ; 2 Aug 4 2003 12:00 AM ; 454 ; NULL
4 vanities4 Aug 5 2003 12:00 AM 4 5 NULL NULL

q444097444
2010-04-04 09:22:40
query can be derived out of this problem, resulting in a lot of related issues.
However, if you do not have self-connection, it is difficult to make a correct answer. Thank you
w25632
2010-04-04 09:29:37
no time to see the previous reply, too much ...
I also gave it a thought, the specific implementation of the scripts do not write
step 1 : Give all merchandise sold within three days and less than the maximum amount of merchandise alarm
SQL: Select Item_Id from sales1
where DateDiff (Day, sale_date, GetDate ( )) <= 3
group by Item_Id
having max (sale_num) <AlarmNum
/ ***** Here I use the is the current date, so use GetDate function ****** /
Step 2: Step1 drawn from the data set you want, and then how you want to handle all right
mention is a guide such as alarm table
do not know if I have mistaken your needs :)
Christ616
2010-04-04 09:38:57
to zyhlhx (Taxuewuhen):
Is not this 8 records Ma?

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 continuously for three consecutive days
2,3,4 three days
3,4,5 for three consecutive days
4,5,6 for three consecutive days

Page 1 of 2    «  »