使用sql server语法优化下面的代码：SELECT cd.Id bh, cd.KeHuBianHao shfzh18, base.queueName pici, GETDATE() pfAddTime, base.KeHuBianHao custId, cd.Xm INTO #CdTable FROM dbo.CardInfo cd LEFT JOIN dbo.Xybase base ON cd.XybaseId = base.Id WHERE cd.Id IN ( SELECT Id FROM ( select c.Id,(select COUNT(1) from XyAddress x with(nolock) where c.Shfzh = x.Shfzh and ISNULL(x.IsDel,0)=0 and x.AddUserId = 'pfAuto') as c_count FROM CardInfo c WHERE c.InPiciId >= '{dateEnd}' AND ISNULL(c.IsDel,0) = 0 ) c WHERE c_count <= 2 );

下面是优化后的 SQL 代码：

WITH cte AS (
SELECT c.Id, c.Shfzh, COUNT(*) AS c_count
FROM dbo.CardInfo c
LEFT JOIN dbo.Xybase b ON c.XybaseId = b.Id
LEFT JOIN dbo.XyAddress a ON c.Shfzh = a.Shfzh AND ISNULL(a.IsDel, 0) = 0 AND a.AddUserId = 'pfAuto'
WHERE c.InPiciId >= '{dateEnd}' AND ISNULL(c.IsDel, 0) = 0
GROUP BY c.Id, c.Shfzh
)
SELECT cd.Id AS bh, cd.KeHuBianHao AS shfzh18, base.queueName AS pici, GETDATE() AS pfAddTime, base.KeHuBianHao AS custId, cd.Xm
INTO #CdTable
FROM dbo.CardInfo cd
LEFT JOIN dbo.Xybase base ON cd.XybaseId = base.Id
WHERE cd.Id IN (
SELECT Id
FROM cte
WHERE c_count <= 2
);

这个 SQL 查询通过一个公共表表达式 CTE 来优化原始查询。首先，我们对 CardInfo 表进行了过滤，并与 Xybase 和 XyAddress 表进行了联接以获取所需的信息。然后，我们使用 CTE 分组并统计了每个身份证号的数量，最后我们使用优化后的查询来选择符合条件的行并将结果保存到 #CdTable 临时表中。