用sqlserver语法优化下面的代码：SELECT cd.Id bh, cd.KeHuBianHao shfzh18, base.queueName pici, GETDATE() pfPhoneTime, 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, CASE WHEN EXISTS ( SELECT 1 FROM XyPhone x WHERE c.Shfzh = x.Shfzh AND ISNULL(x.IsDel,0) = 0 ) THEN 1 ELSE 0 END AS c_count FROM CardInfo c WHERE c.InPiciId = 'pf20220101' AND ISNULL(c.IsDel,0) = 0 ) c WHERE c_count <= 0 );

可以将子查询改为使用 EXISTS，同时将 LEFT JOIN 改为 INNER JOIN，如下所示：

SELECT cd.Id AS bh, cd.KeHuBianHao AS shfzh18, base.queueName AS pici, GETDATE() AS pfPhoneTime, base.KeHuBianHao AS custId, cd.Xm
INTO #CdTable
FROM dbo.CardInfo cd
INNER JOIN dbo.Xybase base ON cd.XybaseId = base.Id
WHERE cd.InPiciId = 'pf20220101'
AND ISNULL(cd.IsDel, 0) = 0
AND NOT EXISTS (
    SELECT 1
    FROM XyPhone x
    WHERE cd.Shfzh = x.Shfzh
    AND ISNULL(x.IsDel, 0) = 0
);

这样可以避免子查询中的重复计算，提高查询效率。同时，使用 INNER JOIN 可以减少 JOIN 操作的数据量，也可以提高效率。

相关问题

使用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 临时表中。