避免子查询

Question

各位同仁， 我有一个查询如下：

SELECT A.ID, B.ID, (HUGE SUBQUERY) as HS
FROM TABLE_A JOIN TABLE_B ON A.ID = B.ID
WHERE (HUGE SUBQUERY) > 0

我想避免重复子查询。 有没有什么方法可以将我的 WHERE 重写为类似

WHERE HS > 0

Or 我必须将子查询转换为联接？

Answer 1

查看 With 子句：

WITH HS AS (Huge subquery)

SELECT A.ID, B.ID, HS
FROM TABLE_A JOIN TABLE_B ON A.ID = B.ID
WHERE HS  > 0

OR

SELECT *
FROM
(
    SELECT A.ID, B.ID, (HUGE SUBQUERY) as HS
    FROM TABLE_A JOIN TABLE_B ON A.ID = B.ID
)
WHERE HS > 0

Answer 2

您可以使用 CTE：

WITH cteHS AS (
    SELECT xxx AS Value
        FROM Huge Subquery
)
SELECT A.ID, B.ID, cteHS.Value as HS
    FROM TABLE_A, cteHS
        JOIN TABLE_B ON A.ID = B.ID
    WHERE cteHS.Value > 0