1. 运输确认--运输证选择 (NOT IN 自连接,内连接)
SELECT Distinct
T0.U_TransportarNo AS '运输证编号' ,
T0.U_FromDate AS '有效期从' ,
T0.U_ToDate AS '有效期到' ,
T0.U_ItemCode AS '物料代码' ,
T0.U_ItemName AS '物料名称',
T0.U_Quantity AS '可用数量' ,
T0.U_Counts as '有效次数'
FROM [@TRANSPORTAR] T0
WHERE T0.U_ItemCode=(
SELECT T1.U_ItemCode
FROM [@TRANSFER_LINES1] T1
WHERE T1.LineId=$[@TRANSFER_LINES2.U_TransferLine]
AND T1.DocEntry=$[@TRANSFER.DocEntry])
AND $[@TRANSFER_LINES2.U_DeliverDate]>=T0.U_FromDate
AND $[@TRANSFER_LINES2.U_DeliverDate]<=T0.U_ToDate
AND T0.U_TransportarNo
NOT IN(
SELECT T0.U_TransportarNo AS '运输证编号'
FROM [@TRANSPORTAR] T0
WHERE
T0.U_Counts=(SELECT COUNT(*) FROM [@TRANSFER_LINES2] WHERE U_LineStatus='C' ))
FOR BROWSE
2. --SQL变量的赋值
declare @now datetime;
select @now=GETDATE();
print @now;
declare @orderNum int;
select @orderNum = COUNT(1) from [@TRANSFER];--这条查询语句只是用来 对 变量进行赋值的,不会返回查询结果的.
print @orderNum;