0.性能低下的。错误的做法 其中 join on 子句中的附加条件“and d.rq between '2025-03-01' and '2025-03-26 23:59:59' and d.ifcheck=0” 是重大问题。

select a.id,a.orderId,a.hid,a.openid,a.amount,a.thirdPayNo,a.payAccountId,a.createTime,a.[state],b.fh,b.cname,c.item_clsname as areaName,d._id payAccountBillNo
from WeixinOrder a
left join payAccountM d on a.payAccountId=d.id and d.rq between '2025-03-01' and '2025-03-26 23:59:59' and d.ifcheck=0
join House b on a.hid = b.id
join TreeInfo c on SUBSTRING(b.itemno, 1, 9) = c.item_clsno
where a.createTime between '2025-03-01' and '2025-03-26 23:59:59' and a.state=0

1.去掉上面加粗部分后，性能起飞

select a.id,a.orderId,a.hid,a.openid,a.amount,a.thirdPayNo,a.payAccountId,a.createTime,a.[state],b.fh,b.cname,c.item_clsname as areaName,d._id payAccountBillNo
from WeixinOrder a
left join payAccountM d on a.payAccountId=d.id
join House b on a.hid = b.id
join TreeInfo c on SUBSTRING(b.itemno, 1, 9) = c.item_clsno
where a.createTime between '2025-03-01' and '2025-03-26 23:59:59' and a.state=0

2.以下采用放到select语句的子查询中，也一样性能起飞。经测试，与上面性能相当。

select a.id,a.orderId,a.hid,a.openid,a.amount,a.thirdPayNo,a.payAccountId,a.createTime,a.[state],b.fh,b.cname,c.item_clsname as areaName,
(select top 1 _id from payAccountM m where m.id=a.payAccountId) payAccountBillNo
from WeixinOrder a
join House b on a.hid = b.id
join TreeInfo c on SUBSTRING(b.itemno, 1, 9) = c.item_clsno
where a.createTime between '2025-03-01' and '2025-03-26 23:59:59' and a.state=0