我想將一個表與 select 陳述句的輸出連接起來，我需要在 select 陳述句的子句中使用last_order_dateTable1 中的列。WHERE
例如，請參閱此螢屏截圖：
我嘗試了以下代碼：
SELECT
*
FROM
Table1 t1
LEFT JOIN
(SELECT prod_id, SUM(sales) sales_sum
FROM Table2
WHERE transaction_date BETWEEN t1.last_order_date AND CAST(GETDATE()-1 AS DATE)
GROUP BY prod_id) t2
ON t1.prod_id = t2.prod_id
顯然，問題是我不能t1.last_order_date 在 join 陳述句右側的 WHERE CLAUSE 中使用。任何人都可以幫忙嗎？
uj5u.com熱心網友回復：
你可以嘗試使用OUTER APPLY
SELECT *
FROM
Table1 t1
OUTER APPLY
(
SELECT t2.prod_id, SUM(t2.sales) sales_sum
FROM Table2 t2
WHERE t2.transaction_date BETWEEN t1.last_order_date AND CAST(GETDATE()-1 AS DATE)
AND t1.prod_id = t2.prod_id
GROUP BY t2.prod_id
) t2
從您的預期結果來看，我認為您可以LEFT JOIN這樣使用。
SELECT t1.prod_id,
MIN(t1.last_order_date) last_order_date,
SUM(ISNULL(t2.sales,0)) sales
FROM
Table1 t1
LEFT JOIN Table2 t2
ON t2.transaction_date BETWEEN t1.last_order_date
AND CAST(GETDATE()-1 AS DATE)
AND t1.prod_id = t2.prod_id
GROUP BY t1.prod_id
轉載請註明出處，本文鏈接：https://www.uj5u.com/qukuanlian/456566.html