CREATE PROC spGetDat @Qty INT AS SELECT a.ID,a.Qty,a.HName, a.Date,b.LTime FROM table1 a,table2 b WHERE a.HName=b.HName AND a.Date<DateAdd(day,b.LTime,GETDATE()) AND a.Qty<@Qty
--LTime是天数? create procedure GetData(@qty int) as begin select * from [table1] a join [table2] b on a.HName=b.HName where exists(select 1 from [table1] where [Date]<DATEADD(DD,b.LTime,GETDATE()) and Qty<@qty) endgo exec GetData 5--执行
--这样? create proc wsp @參數 int as select a.* from (select a.*,b.ltime,etime=dateadd(dd,b.ltime,getdate()) from table1 a,table2 b where a.hname=b.hname)a,table1 b where b.date<a.etime and a.Qty<@參數 go
create procedure GetData(@qty int) as begin select * from [table1] a join [table2] b on a.HName=b.HName where a.[Date]<DATEADD(DD,b.LTime,GETDATE()) and a.Qty<@qty endgo exec GetData 10--执行
create proc P1(@Qty int) as select t1.*,t2.LTime from table1 t1 join table2 t2 on t1.HName=t2.HName where datediff(d,t1.[Date],getdate()+LTime)<=0--包括當天 and t1.Qty<@Qty
Date <(今天+table3.LTime)create proc P1(@Qty int) as select t1.*,t2.LTime from table1 t1 join table2 t2 on t1.HName=t2.HName where datediff(d,t1.[Date],getdate()+LTime)>=0--包括當天 and t1.Qty<@Qty
SQL SERVER 2005select t3.* from table1 t3 where exists (select 1 from table1 t1 on t1.Id = t3.Id and t1.date < GetDate() + t3.LTime) and t3.Qty < @參數
@Qty INT
AS
SELECT a.ID,a.Qty,a.HName,
a.Date,b.LTime
FROM table1 a,table2 b
WHERE a.HName=b.HName
AND a.Date<DateAdd(day,b.LTime,GETDATE())
AND a.Qty<@Qty
--LTime是天数?
create procedure GetData(@qty int)
as
begin
select * from [table1] a join [table2] b on a.HName=b.HName
where exists(select 1 from [table1] where [Date]<DATEADD(DD,b.LTime,GETDATE())
and Qty<@qty)
endgo
exec GetData 5--执行
--这样?
create proc wsp
@參數 int
as
select a.* from
(select a.*,b.ltime,etime=dateadd(dd,b.ltime,getdate()) from table1 a,table2 b where a.hname=b.hname)a,table1 b
where b.date<a.etime and a.Qty<@參數
go
as
begin
select * from [table1] a join [table2] b on a.HName=b.HName
where a.[Date]<DATEADD(DD,b.LTime,GETDATE())
and a.Qty<@qty
endgo
exec GetData 10--执行
as
select
t1.*,t2.LTime
from
table1 t1
join
table2 t2 on t1.HName=t2.HName
where
datediff(d,t1.[Date],getdate()+LTime)<=0--包括當天
and
t1.Qty<@Qty
as
select
t1.*,t2.LTime
from
table1 t1
join
table2 t2 on t1.HName=t2.HName
where
datediff(d,t1.[Date],getdate()+LTime)>=0--包括當天
and
t1.Qty<@Qty
where exists (select 1 from table1 t1 on t1.Id = t3.Id and t1.date < GetDate() + t3.LTime)
and t3.Qty < @參數