调整查询以使用CTE

Question

我想在查询中使用CTE，因为我得到了相同的选择，但我以前从未使用过CTE，有人能帮我吗？

我需要第一个表可以在其他选择中使用

SELECT firstTable.id as id,
secondTable.holder as holder
FROM (
select tb3.id as id
from table1 tb1
inner join table2 tb2 on tb1.tb2_id = tb2.id
inner join table3 tb3 on tb2.tb3_id = tb3.id and tb3.id
inner join table4 tb4 on tb4.id = tb3.tb4_id and tb4.id = 1998
group by tb3.id) as firstTable
JOIN (
select id_holder,
sum(temporaryTable.holder) as holder
from (
select cast(tb4.helper as integer) as helper,
count(distinct tb4.id) as holder,
tb3.id as id_holder
from table1 tb1
inner join table2 tb2 on tb1.tb2_id = tb2.id
inner join table3 tb3 on tb2.tb3_id = tb3.id and tb3.id
inner join table4 tb4 on tb4.id = tb3.tb4_id and tb4.id = 1998
group by tb3.id, tb4.helper
) as temporaryTable
where temporaryTable.helper between 7 and 8
group by id_holder) as secondTable
ON firstTable.id = temporaryTable.id_holder

这是我尝试的查询：

WITH temporary_table AS
( SELECT id,
cast(resp.valor AS integer) AS holder,
count(val) AS sumId
FROM table1 tb1
INNER JOIN table2 tb2 ON tb1.tb2_id = tb2.id
INNER JOIN table3 tb3 ON tb2.tb3_id = tb3.id
AND tb3.id
INNER JOIN table4 tb4 ON tb4.id = tb3.tb4_id
AND tb4.id = 1998
GROUP BY tb3.id )
SELECT
(SELECT SUM(sumId)
WHERE holder = -10) AS p1,
(SELECT SUM(sumId)
WHERE holder = 78) AS p2,
(SELECT SUM(sumId)
WHERE holder = 1997) AS p3,
id
FROM temporary_table
GROUP BY id,
holder;

Answer 1

尝试此查询：

select tb3.id as id,
count(distinct CASE WHEN tb4.helper between 7 and 8 THEN tb4.id END) as holder,
from table1 tb1
inner join table2 tb2 on tb1.tb2_id = tb2.id
inner join table3 tb3 on tb2.tb3_id = tb3.id and tb3.id
inner join table4 tb4 on tb4.id = tb3.tb4_id and tb4.id = 1998
group by tb3.id

Answer 2

对每个子查询使用CTE非常简单：

WITH firstTable AS (
select tb3.id as id
from table1 tb1
inner join table2 tb2 on tb1.tb2_id = tb2.id
inner join table3 tb3 on tb2.tb3_id = tb3.id and tb3.id
inner join table4 tb4 on tb4.id = tb3.tb4_id and tb4.id = 1998
group by tb3.id
), temporaryTable AS (
select cast(tb4.helper as integer) as helper,
count(distinct tb4.id) as holder,
tb3.id as id_holder
from table1 tb1
inner join table2 tb2 on tb1.tb2_id = tb2.id
inner join table3 tb3 on tb2.tb3_id = tb3.id and tb3.id
inner join table4 tb4 on tb4.id = tb3.tb4_id and tb4.id = 1998
group by tb3.id, tb4.helper
), secondTable AS (
select id_holder,
sum(temporaryTable.holder) as holder
from temporaryTable
where temporaryTable.helper between 7 and 8
group by id_holder
)
SELECT firstTable.id as id,
secondTable.holder as holder
FROM firstTable
JOIN secondTable
ON firstTable.id = temporaryTable.id_holder;

Answer 3

我只提供问题本身的切线。

我强烈建议您不要使用CTE。如果你想要一个看起来不错的优雅查询，我相信最好只是正确地格式化你的查询。http://www.dpriver.com/pp/sqlformat.htm

CTE有一个固有的问题，即速度慢，因为不知何故，指数在大多数时候都没有正确触发。多年来，我一直从事SQL这一职业，在CTE中遇到了很多性能问题。使CTE更快是困难的，我认为不值得付出努力。它们也让调试和测试查询变得很痛苦，尤其是当CTE中有很多表时。很难划分子查询，尤其是测试前一个子查询的假设是否仍然有效。

您也可以在stackoverflow中查看以下至少500多个关于CTE缓慢的问题：https://stackoverflow.com/search?q=slow+cte

我的建议是将数据放在临时表中。您还可以通过重用临时表来删除重复的代码。为了获得更好的性能，您可以在临时表中设置索引。不能在CTE中创建动态索引。在我看来，使用CTE的唯一原因是递归CTE。https://www.essentialsql.com/recursive-ctes-explained/