所以我有兩張桌子：
CREATE TABLE table1
(`PRIMARY_DT` date, `CUST_ID` int)
;
INSERT INTO table1
(`PRIMARY_DT`, `CUST_ID`)
VALUES
('2012-03-02', 878 ),
('2012-07-02', 456 ),
('2012-09-02', 789 )
;
CREATE TABLE table2
(`dt` date, `CUST_ID` int, `value` int)
;
INSERT INTO table2
(`dt`, `CUST_ID`, `value`)
VALUES
('2012-03-8', 878, 1)
;
如果滿足條件（表 2 中的日期在表 1 中的日期的 7 天內），我需要將表 2 中的值添加到表 1。如果不是這種情況，則只需添加 -9999 的值。所以是這樣的：
| PRIMARY_DT | CUST_ID | 價值 |
|---|---|---|
| '2012-03-02' | 878 | 1 |
| '2012-03-02' | 456 | -9999 |
| '2012-03-02' | 789 | -9999 |
到目前為止，如果滿足以下代碼條件，我可以加入：
SELECT t1.PRIMARY_DT,
t1.CUST_ID,
t2.value
FROM table1 t1
JOIN table2 t2 ON t1.CUST_ID = t2.CUST_ID
WHERE t2.value = (SELECT MIN(tt2.value)
FROM table2 tt2
WHERE tt2.dt BETWEEN t1.PRIMARY_DT AND t1.PRIMARY_DT 7)
我嘗試在沒有匹配但我有隧道視野的情況下添加 -9999。下面給出與上面相同的結果：
SELECT t1.PRIMARY_DT,
t1.CUST_ID,
t2.value
FROM table1 t1
JOIN table2 t2 ON t1.CUST_ID = t2.CUST_ID
WHERE t2.value = (CASE WHEN
(SELECT MIN(tt2.value)
FROM table2 tt2
WHERE tt2.dt BETWEEN t1.PRIMARY_DT AND t1.PRIMARY_DT 7) IS NULL THEN
-9999 ELSE (SELECT MIN(tt2.value)
FROM table2 tt2
WHERE tt2.dt BETWEEN t1.PRIMARY_DT AND t1.PRIMARY_DT 7) END)
任何一個查詢都只給我：
| PRIMARY_DT | CUST_ID | 價值 |
|---|---|---|
| '2012-03-02' | 878 | 1 |
uj5u.com熱心網友回復：
您可以通過以下方式在很大程度上簡化您的解決方案
LEFT JOIN在兩個表之間使用 aCASE在一個陳述句中應用日期條件
SELECT t1.*,
CASE WHEN DATEDIFF(t2.dt, t1.PRIMARY_DT) < 7
THEN t2.value
ELSE -9999
END AS value
FROM table1 t1
LEFT JOIN table2 t2
ON t1.CUST_ID = t2.CUST_ID
在此處查看演示。
uj5u.com熱心網友回復：
因此，發布此訊息后，我的隧道視野消失了，這是答案：
SELECT t1.PRIMARY_DT,
t1.CUST_ID,
CASE WHEN (SELECT MIN(tt2.value)
FROM table2 tt2
WHERE tt2.dt BETWEEN t1.PRIMARY_DT AND t1.PRIMARY_DT 7) IS NULL
THEN 0 ELSE (SELECT MIN(tt2.value)
FROM table2 tt2
WHERE tt2.dt BETWEEN t1.PRIMARY_DT AND t1.PRIMARY_DT 7) END
FROM table1 t1
LEFT OUTER JOIN table2 t2 ON t1.CUST_ID = t2.CUST_ID
轉載請註明出處，本文鏈接：https://www.uj5u.com/qiye/517281.html
上一篇：sql查詢追加 或-和前導零