How does SQL Server use a CTE to sort by sub-query?

How does SQL Server use a CTE to sort by sub-query?

Requirements: Identify recently changed customer information (sorted by last change time, from Systemlog table Logdatetime field)

Description

Customer: Client Information Form
Systemlog: System Log table, record all table information increment, delete, change

The development of their own industry software, not only their own companies are in use, other companies are also in use, the company software maintenance and provide upgrade services
Since the previous design Customer information table did not change the Time field, the system is now running in n different databases
Causes the database structure to not be arbitrarily changed (although it can be upgraded, but too expensive)

You cannot use views and stored procedures to use only SQL statements

I wrote the SQL and the effect, although can achieve effect, but Sqlsrever execution plan and IO situation is very bad

(Personal understanding Systemlog query again)

--Customer information sorted by last changed time:

12345678

WITH _temp AS(SELECTCustomerId,CompanyName,(SELECT TOP 1 LogDateTime FROM SystemLog WHERE LogType=‘更改‘ AND LogTable=‘Customer‘ AND Rid=CustomerId ORDER BY ID DESC )AS ed ,ROW_NUMBER() OVER ( ORDER BY (SELECT TOP 1 LogDateTime FROM SystemLog WHERE LogType=‘更改‘AND LogTable=‘Customer‘ AND Rid=CustomerId ORDER BY ID DESC ) DESC ) as RowNumberFROM CustomerWHERE CustomerStatus=18 )SELECT * FROM _temp WHERE RowNumber BETWEEN 0 AND 10 ORDER BY ed DESC

　　

System log for querying customer information

SELECTTOPTen S.ridAsCustomerId, CompanyName, S.logdatetimeFrom Systemlog s left join Customer c on s.rid=where logtype=  '  change  "and logtable=  Customer ' and C.customerstatus =18by s.id desc       

Set STATISTICS IO on;

(10 rows affected)
Table ' Systemlog '. Scan count 2, logical read 1697 times, physical read 0 times, read 7 times, LOB logic read 0 times, lob physical read 0 times, lob read 0 times.
Table ' worktable '. Scan Count 155, logical read 50,916 times, physical read 0 times, read 0 times, LOB logic read 0 times, lob physical read 0 times, lob read 0 times.
Table ' Customer '. Scan count 1, logical read 19 times, physical read 0 times, read 1 times, LOB logic read 0 times, lob physical read 0 times, lob read 0 times.

(1 rows affected)

(10 rows affected)
Table ' Customer '. Scan count 0, logical read 157 times, physical read 0 times, read 0 times, LOB logic read 0 times, lob physical read 0 times, lob read 0 times.
Table ' Systemlog '. Scan count 1, logical read 73 times, physical read 0 times, read 0 times, LOB logic read 0 times, lob physical read 0 times, lob read 0 times.

(1 rows affected)

May I ask what optimization method you have? Thank you very much

How does SQL Server use a CTE to sort by sub-query?