我有一個帶有安裝日期和客戶的表“設備”：
| ID | 設備名稱 | 已安裝 | 顧客 |
|---|---|---|---|
| 1 | 一種 | 2021-10-10 | 1 |
| 2 | 乙 | 2021-10-10 | 1 |
| 3 | 一種 | 2021-10-20 | 2 |
| 4 | 一種 | 2021-10-30 | 3 |
我需要以下結果
| 設備名稱 | 安裝自 | 安裝到 | 顧客 |
|---|---|---|---|
| 一種 | 2021-10-10 | 2021-10-20 | 1 |
| 乙 | 2021-10-10 | 空值 | 1 |
| 一種 | 2021-10-20 | 2021-10-30 | 2 |
| 一種 | 2021-10-30 | 空值 | 3 |
用下面的SQL試過
select a.DeviceName, a.Installed as InstalledFrom, b.installed as InstalledTo, a.customer
from Device a
left outer join Device b on ( b.DeviceName = ( SELECT DeviceName
FROM Device
where DeviceName = a.DeviceName
AND Installed > a.Installed
order by installed limit 1) )
但這并沒有給出預期的結果。
謝謝你的幫助！
uj5u.com熱心網友回復：
測驗這個：
SELECT t1.DeviceName,
t1.Installed InstalledFrom,
t2.Installed InstalledTo,
t1.Customer
FROM Device t1
LEFT JOIN Device t2 ON t1.DeviceName = t2.DeviceName
AND t1.Installed < t2.Installed
WHERE NOT EXISTS ( SELECT NULL
FROM Device t3
WHERE t1.DeviceName = t3.DeviceName
AND t1.Installed < t3.Installed
AND t3.Installed < t2.Installed )
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/331509.html