求比較複雜的mssql的存儲過程

[cyz9977]

兩個表：table1，table2
table1字段：
ID Qty HName Date
A01 10 ACT 2008-9-1
A02 3 DSV 2008-9-12
A03 7 ZDW 2008-9-23
A04 22 SDF 2008-10-6
... ... ... ....

table2字段：
HName LTime
ACT 30
DSV 20
ZDW 10
SDF 5
... ...

table1關聯table2（通過HName關聯）叫table3吧
ID Qty HName Date LTime
A01 10 ACT 2008-9-1 30
A02 3 DSV 2008-9-12 20
A03 7 ZDW 2008-9-23 10
A04 22 SDF 2008-10-6 5

求：取table3中某條記錄時，如果table1中的Date<（今天+table3.LTime）的記錄中存在某條記錄的Qty<@參數，就取出table3中該記錄
（求存儲過程，SqlServer2005）

[pt1314917]

求：取table3中某條記錄時，如果table1中的Date <（今天+table3.LTime）的記錄中存在某條記錄的Qty <@參數，就取出table3中該記錄
--
没看懂

[-晴天]

LTime 是什么?天数?

[Garnett_KG]

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

[wxg22526451]

--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)
end

go
exec GetData 5--执行

[pt1314917]

--这样？
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

[wxg22526451]

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
end

go
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

[tootto]

SQL SERVER 2005

select 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 < @參數