sql - 在另一个CTE中调用一个CTE

如何在另一个CTE中称呼CTE？

WITH cte1
AS (
    SELECT City.*
    FROM City
    WHERE (City.CityName COLLATE SQL_Latin1_General_CP1_CI_AI) LIKE 'são paulo'
)
, cte2
AS (
    SELECT Imovel.Imovel_Id
    FROM Imovel
    WHERE Imovel.Number = 311
    AND Imovel.ZIPCode = '30280490'
    AND Imovel.Complement = ''
    AND Imovel.Street = 'Do furquim'
        -- the line below has an error in cte.City_Id
    AND Imovel.City_Id = cte1.City_Id
)

最佳答案

您必须像使用普通表一样将两者连接在一起:

WITH cte1
     AS (SELECT city.*
         FROM   city
         WHERE  ( city.cityname COLLATE sql_latin1_general_cp1_ci_ai ) LIKE
                'são paulo'),
     cte2
     AS (SELECT imovel.imovel_id
         FROM   imovel
                INNER JOIN cte1
                        ON imovel.city_id = cte1.city_id
         WHERE  imovel.number = 311
                AND imovel.zipcode = '30280490'
                AND imovel.complement = ''
                AND imovel.street = 'Do furquim')
SELECT * FROM   cte2

请注意，由于CTE无法单独“站立”，因此我附加了SELECT * FROM cte2。

关于sql - 在另一个CTE中调用一个CTE，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/17425156/