需關聯 order_info、ivrlog4ivrdlvinst、delivery_info 三張表,依條件篩選並取得最新記錄。
SELECT * FROM order_info AS a, ivrlog4ivrdlvinst AS b
WHERE a.saleorder = b.ext1_skill
AND b.start_date = @date1
AND se_id = '55'
AND b.ext1_skill != ''
AND CONVERT(varchar(10), a.instime, 112) = @date2
AND MAX(a.instime); -- 此處語法有誤,MAX 不能直接用於 WHERE
SELECT * FROM order_info AS a
WHERE a.saleorder = (
SELECT b.ext1_skill FROM ivrlog4ivrdlvinst AS b
WHERE b.start_date = @date1 AND se_id = '55' AND b.ext1_skill != ''
)
AND CONVERT(varchar(10), MAX(a.instime), 112) = @date2; -- 同樣語法錯誤
DECLARE @date1 VARCHAR(20), @date2 VARCHAR(20);
SET @date1 = '20100812'; SET @date2 = '2010-08-12';
SELECT * FROM order_info AS a
WHERE a.saleorder = (
SELECT b.ext1_skill FROM ivrlog4ivrdlvinst AS b
WHERE b.start_date = @date1 AND se_id = '55' AND b.ext1_skill != ''
)
AND CONVERT(varchar(10), a.instime, 112) = @date2
AND MAX(a.instime); -- 仍有語法問題
SELECT b.caller, b.start_date, b.start_time, b.ext1_skill,
c.deliveryno, c.destroyresult, c.deliverydate, c.deliverytime,
c.arrangetime, c.driverphone, c.drivermobile,
a.servicedate, a.servicetime, a.workertel
FROM order_info AS a
JOIN ivrlog4ivrdlvinst AS b ON a.saleorder = b.ext1_skill
JOIN delivery_info AS c ON a.saleorder = c.orderid -- 假設關聯鍵
WHERE b.start_date = @date1
AND b.se_id = '55'
AND b.ext1_skill != ''
AND CONVERT(varchar(10), a.instime, 112) = @date2
ORDER BY b.start_date DESC, b.start_time DESC;
JOIN 語法,避免隱式連接。=,多值用 IN 或 EXISTS。