MariaDB/Mysql SELECT from - to with Subquery-CodePudding

I have a table 'Device' with an installed date and a customer:

Id	DeviceName	Installed	Customer
1	A	2021-10-10	1
2	B	2021-10-10	1
3	A	2021-10-20	2
4	A	2021-10-30	3

I need following results

DeviceName	InstalledFrom	InstalledTo	Customer
A	2021-10-10	2021-10-20	1
B	2021-10-10	NULL	1
A	2021-10-20	2021-10-30	2
A	2021-10-30	NULL	3

Tried it with following 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) )

but this does not give the desired result.

Thanks for any help!

CodePudding user response：

Test this:

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 )