@spdmSelect isNull(b.dj,0) from rkdb a join rkmxb b on a.rkdh = b.rkdh
where a.kdrq = (
Select max(aa.kdrq) from rkdb aa join rkmxb bb on aa.rkdh = bb.rkdh
where aa.kdrq < getdate() and bb.spdm = @spdm
)
where a.kdrq = (
Select max(aa.kdrq) from rkdb aa join rkmxb bb on aa.rkdh = bb.rkdh
where aa.kdrq < getdate() and bb.spdm = @spdm
)
Select isNull(b.dj,0) from rkdb a join rkmxb b on a.rkdh = b.rkdh
where a.kdrq = (
Select max(aa.kdrq) from rkdb aa join rkmxb bb on aa.rkdh = bb.rkdh
where and bb.spdm = y.spdm ) as 估价
from rkmxb y where rkdh = @rkdh
from rkmxb T
where dj =(select min(dj)
from rkmxb
where t.spdm = spdm
and t.rkdh = (select max(rkdh)
from rkdb t1
where kdrq = (select max(kdrq)
from rkdb
where rkdh = t1.rkdh
)
)
)
还是要定义一个存储过程,传人的参数是rkdh,然后用游标或临时表,循环取出每种材料的单价
CREATE PROC GetDj(@rkdh NVARCHAR(50))
AS
BEGIN
DECLARE @spdm NVARCHAR(50)
DECLARE @Rq DATETIME
DECLARE @TempRkdh NVARCHAR(50)
DECLARE @dj FLOAT DECLARE CUR_1 CURSOR FOR
SELECT spdm FROM 明细表 WHERE rkdh = @rkdh
OPEN CUR_1
FETCH CUR_1 INTO @spdm
WHILE (@@FETCH_STATUS = 0)
BEGIN
--取出最大日期
SELECT @Rq = MAX(A.kdrq) FROM 主表 A INNER JOIN 明细表 B
ON A.rkdh = B.rkdh WHERE B.spdm = @spdm --取最大日期的编号
SELECT @TempRkdh = rkdh FROM 主表 WHERE kdrq = @Rq SET @dj = 0 --这句不可丢
--取出单价
SELECT TOP 1 @dj = dj FROM 明细表 WHERE rkdh = @TempRkdh
AND spdm = @spdm
--更新单价
UPDATE 明细表 SET dj = @dj WHERE rkdh = @rkdh AND spdm = @spdm FETCH CUR_1 INTO @spdm
END
CLOSE CUR_1DEALLOCATE CUR_1 END没办法测试,你自己试试,大概思路是这样
from #rkdb
left join #rkmxb t on #rkdb.rkdh = t.rkdh
and t.dj =(select min(dj)
from #rkmxb
where t.spdm = spdm )
from #rkdb
left join #rkmxb t on #rkdb.rkdh = t.rkdh
where #rkdb.kdrq =(select max(#rkdb.kdrq)
from #rkdb left join #rkmxb on #rkdb.rkdh = #rkmxb.rkdh
where t.spdm = #rkmxb.spdm
)
from #rkdb
left join #rkmxb t on #rkdb.rkdh = t.rkdh
where #rkdb.kdrq =(select max(#rkdb.kdrq)
from #rkdb
left join #rkmxb t1 on #rkdb.rkdh = t1.rkdh
where t.spdm = t1.spdm
)
and dj = (select min(dj)
from #rkmxb
where spdm = t.spdm
)