一次查询计算返回多个值-APPLY


Warning: Undefined array key "HTTP_REFERER" in /www/wwwroot/prod/www.enjoyasp.net/wp-content/plugins/google-highlight/google-hilite.php on line 58
问题: 取出公司销售员工最近的订单编号及订单日期
 老办法,多次取值。实际是重复查询,查询一次返回一个值,不能做到查询一次得到所有结果
   SELECT a.UserName,
             (SELECT TOP 1 OrderNo
                FROM bdorder b
                WHERE b.SalesStaff = a.account
                  AND b.OrderDate >='2011-02-01'
                ORDER BY b.OrderNo DESC
              ) OrderNo,
             
                (SELECT TOP 1 OrderDate
                 FROM bdorder b
                 WHERE b.SalesStaff = a.account
                   AND b.OrderDate >='2011-02-01'
                 ORDER BY b.OrderNo DESC
                 )OrderDate 
                
 FROM frmuser a

新方法:查询一次返回一个结果集
SELECT a.UserName,b.OrderNo,b.orderdate
FROM frmuser a(NOLOCK)
  OUTER  APPLY(
      SELECT TOP 1 OrderNo,OrderDate
      FROM bdorder b
      WHERE b.SalesStaff = a.account
      AND b.OrderDate >='2011-02-01'
      ORDER BY b.OrderDate DESC
    )b
OUTER APPLY:相当于表中的LEFT JOIN
CROSS APPLY:相当于表中的JOIN