Sqlserver 使用CTE如何按子查询排序?

2024-01-16 19:55:52

需求:查出最近有更改的客户信息(按最后更改时间排序,来自SystemLog表LogDateTime字段)

说明：

Customer:客户信息表
SystemLog:系统日志表,记录所有表信息的增,删,改

自己公司开发的行业软件,不仅自己公司有在用,其他公司也在用,本公司进行软件维护和提供升级服务
由于之前的设计客户信息表并没有更改时间字段,而现在该系统已经在N个不同的数据库里运行
导致不能随意更改数据库结构(虽然可以升级,但代价太大)

不能使用视图及存储过程只能使用Sql语句

我写的Sql及效果,虽然可以实现效果,但Sqlsrever的执行计划和IO情况很糟糕

(个人理解SystemLog查询了再次)

--客户信息按照最后更改时间排序：

WITH _temp AS(SELECT
CustomerId
,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 RowNumber
FROM Customer
WHERE CustomerStatus=18 
)SELECT * FROM _temp WHERE RowNumber BETWEEN 0 AND 10 ORDER BY ed DESC

查询客户信息的系统日志

SELECT TOP 10 s.Rid AS CustomerId, CompanyName , s.LogDateTime 
FROM SystemLog s 
LEFT JOIN Customer c ON s.Rid=CustomerId 
WHERE LogType=‘更改‘AND LogTable=‘Customer‘ 
AND c.CustomerStatus=18
ORDER BY s.id DESC

set statistics io on;

(10 行受影响)
表 ‘SystemLog‘。扫描计数 2，逻辑读取 1697 次，物理读取 0 次，预读 7 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
表 ‘Worktable‘。扫描计数 155，逻辑读取 50916 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
表 ‘Customer‘。扫描计数 1，逻辑读取 19 次，物理读取 0 次，预读 1 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。

(1 行受影响)

(10 行受影响)
表 ‘Customer‘。扫描计数 0，逻辑读取 157 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
表 ‘SystemLog‘。扫描计数 1，逻辑读取 73 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。