SQL查询的运用

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;