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