公用表表达式（CTE）对SQL语句执行效率的影响。

本文链接：https://blog.youkuaiyun.com/weixin_43255518/article/details/103894592

这篇博客探讨了在MySQL中使用公用表表达式（CTE）对SQL查询性能的影响。通过比较两种不同的查询方式，发现使用CTE的查询在执行时间上显著优于不使用CTE的查询，从1s多降低到仅10几毫秒。文章通过具体的源代码实例展示了CTE如何优化查询效率。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

#源代码1：
select t.*, t.data_resource MAT_CLASS
from (SELECT A.data_resource, A.MAT_CODE, A.COMPANY_CODE
FROM XGIRP1.TYMIR22 A
WHERE A.COMPANY_CODE = ‘XISC’
and A.data_resource like ‘%条码打印%’
UNION
SELECT DISTINCT B.MAT_CLASS, B.MAT_CODE, B.COMPANY_CODE
FROM XGIRP1.TQMIR0A B
WHERE B.MAT_CODE != ’ ') t
WHERE t.MAT_CODE IN
(select mat_code
from (SELECT A1.data_resource, A1.MAT_CODE, A1.COMPANY_CODE
FROM XGIRP1.TYMIR22 A1
WHERE A1.COMPANY_CODE = ‘XISC’
and A1.data_resource like ‘%条码打印%’
UNION
SELECT DISTINCT B1.MAT_CLASS, B1.MAT_CODE, B1.COMPANY_CODE
FROM XGIRP1.TQMIR0A B1
WHERE B1.MAT_CODE != ’ ') t1
group by t1.mat_code
having count(t1.mat_code) > 1)
AND t.COMPANY_CODE = ‘XISC’
执行时间：service end. 1136ms elapsed.时间1s多。
#源代码2：
WITH temp AS
(SELECT A.data_resource, A.MAT_CODE, A.COMPANY_CODE
FROM XGIRP1.TYMIR22 A
WHERE A.COMPANY_CODE = ‘XISC’
and A.data_resource like ‘%条码打印%’
UNION
SELECT DISTINCT B.MAT_CLASS, B.MAT_CODE, B.COMPANY_CODE
FROM XGIRP1.TQMIR0A B
WHERE B.MAT_CODE != ’ ')

select t.*, t.data_resource MAT_CLASS
from temp t
WHERE t.MAT_CODE IN
(select mat_code
from temp t1
group by t1.mat_code
having count(t1.mat_code) > 1)
AND t.COMPANY_CODE = ‘XISC’
执行时间：service end. 14ms elapsed.时间10几毫秒。