将SQL cte优化为一个

Question

我有三个cte，它们都使用相同的源表table1同样的连接条件。我想把这三个合并到一个CTE中，以实现性能并有效地获得结果集。

如何在不影响结果集的情况下做到这一点?它们之间唯一的区别是WHERE子句，我想合并WHERE子句，使整个CTE成为一个

acc1 AS (
select
ta.file, 
case when users.ename is null
then ta.recipient 
else concat(users.ename, '[', ta.recipient, ']') end as email_to 
from schema1.table1 ta
left join users on ta.recipient = users.LDAP_id 
where ta.action like 'X' 
and ta.recipient not like ' '
and ta.file in (select file from head)
),
acc2 AS (
select
ta.file,
isnull(
string_agg (
case when users.ename is null
then ta.recipient
else concat(users.ename,' [', ta.recipient, ']') end,
'; '
),
''
) as copy_to
from schema1.table1 ta
left join users on ta.recipient = users.LDAP_id
where ta.usr_flag like 'X'
and ta.file in (select file from head)
group by
ta.file
), 

acc3 AS (
select
ta.file, 
isnull(
case when users.ename is null
then ta.recipient
else concat(users.ename, concat(users.ename,' [', ta.recipient, ']') end,
""
) as reserved_by
from schema1.table1 ta
left join users on ta.recipient = users.LDAP_id 
where ta.reserved like 'X'
and ta.file in (select file from head)
)

我试图把这些列放到一个选择中，但是我知道如何应用过滤器

Answer 1

您建议的重构可能不是获得良好查询效率所必需的。查询规划器在优化复杂查询方面做得非常好。它知道如何使用cte将整体查询转换为抽象数据流树，然后优化这些数据流以使用适当的索引。您可以将(非递归)cte视为编写嵌套查询的简单语法——这就是查询规划器处理它们的方式。

最好避免重构:你所拥有的都是经过测试并正常工作的。

重构查询仍然有可能有所帮助。但是还有其他事情你应该先做。

1. 添加适当的索引。如果您使用SSMS (Microsoft的SQL Server Management Studio)，这个技巧适合您:在查询窗口中右键单击，然后选择Show Actual Execution Plan，然后运行查询。执行计划显示有时会建议创建一个新索引。

   创建索引，然后再次尝试实际执行计划，看看它是否在第一个索引的基础上提出了另一个建议。通常SSMS建议的索引可以解决您的性能问题。

2. 读这篇文章，然后问一个具体的问题，要求帮助你的实际执行计划。

3. 这个分析可能会导致你重构你的查询。

话虽如此，您可以通过合并两个cte的SELECT子句来组合a和c。b是一个聚合(GROUP BY)查询，因此它不能与其他查询合并。它的结果集本身具有不同的行数，因此，除非您知道如何将它连接到其他表，否则无法开始合并它。

Answer 2

您想如何合并这些数据还不清楚。假设您只想要一个UNION ALL，那么合并a和c是相当容易的，但是b更复杂，需要相当复杂的分组构造，可能不值得合并。

注意一些case表达式被简化了。

merged_a_and_b AS (
select
ta.document,
case when ta.action like 'X'
then isnull(
users.ename + '[' + ta.recipient + ']',
ta.recipient
)
end as email_to
case when ta.reserved not like 'X'
then isnull(
isnull(
users.ename + '[' + ta.recipient + ']',
ta.recipient
),
""
)
end as reserved_by
from schema1.table1 ta
left join users on ta.recipient = users.LDAP_id 
where (
ta.action like 'X'  and ta.recipient not like ' '
or ta.reserved like 'X'
)
and ta.document in (select document from head)
)

假设您想要更多的连接样式而不是联合样式，这要简单得多，特别是如果您可以聚合每个ta.document

的所有行。

merged AS (
select
ta.document,
string_agg(
case when ta.action like 'X' and ta.recipient not like ' '
then isnull(
users.ename + '[' + ta.recipient + ']',
ta.recipient
)
end,
'; '
) as email_to, 
isnull(
string_agg (
case when users.ename is null and ta.usr_flag like 'X'
then ta.recipient
else concat(users.ename,' [', ta.recipient, ']') end,
'; '
),
''
) as copy_to,
case when ta.reserved like 'X'
then isnull(
string_agg(
isnull(
users.ename + users.ename + ' [' + ta.recipient + ']',
ta.recipient
),
''
)
)
end as reserved_by
from schema1.table1 ta
left join users on ta.recipient = users.LDAP_id 
where ta.reserved like 'X'
where ta.document in (select document from head)
)