sql - 避免子查询

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

或者我必须将我的子查询变成一个连接？

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

或

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