SQL Server 2019查询报错：Invalid object name 'P' 求助

需求说明 #

编写查询语句，显示2015年1月至2016年1月期间居住在Bangalore或Pune的所有租户的profile id、全名、电话、邮箱、城市、房屋id、入住日期、退房日期、租金、推荐总数、最新雇主及职业类别，并按租金降序排序。

原查询代码及报错 #

用户编写的查询代码如下：

With P as
(
select profile_id, first_name+ ' '+ last_name as Full_Name, phone, email_id, city
from Profiles$ 
where city in ('Bangalore','Pune')),
TH as (
select profile_id, house_id, move_in_date, move_out_date, rent
from Tenancy_History
where move_in_date >= '2015-01-01' AND move_in_Date<= '2016-01-31'),
ES as (
select profile_id, latest_employer, occupational_category
from Employment_Details$),
R as (
select profile_id, sum(referral_valid) as "Total Referral"
from Referral$
group by profile_id)


select P.profile_id, P.Full_Name, P.phone, P.email_id, P.city, TH.house_id, TH.move_in_date, TH.move_out_date, TH.rent, R.Total Referral, ES.latest_employer, ES.occupational_category
from P
inner join TH on 
P.profile_id=TH.profile_id
inner join ES on
P.profile_id=ES.profile_id
inner join R on
P.profile_id=R.profile_id

运行后报错：Invalid object name 'P'

错误原因及修正方案 #

错误原因 #

1. 列名语法错误：查询中的R.Total Referral写法违规，列名包含空格时，必须用方括号[Total Referral]包裹，否则数据库会将其解析为两个独立标识符，引发语法错误，进而导致CTE无法被正确识别。
2. 缺少排序逻辑：原代码未实现需求中“按租金降序排序”的要求。

修正后的查询代码 #

WITH P AS (
    SELECT 
        profile_id, 
        first_name + ' ' + last_name AS Full_Name, 
        phone, 
        email_id, 
        city
    FROM Profiles$ 
    WHERE city IN ('Bangalore','Pune')
),
TH AS (
    SELECT 
        profile_id, 
        house_id, 
        move_in_date, 
        move_out_date, 
        rent
    FROM Tenancy_History
    WHERE move_in_date >= '2015-01-01' AND move_in_Date <= '2016-01-31'
),
ES AS (
    SELECT 
        profile_id, 
        latest_employer, 
        occupational_category
    FROM Employment_Details$
),
R AS (
    SELECT 
        profile_id, 
        SUM(referral_valid) AS "Total Referral"
    FROM Referral$
    GROUP BY profile_id
)

SELECT 
    P.profile_id, 
    P.Full_Name, 
    P.phone, 
    P.email_id, 
    P.city, 
    TH.house_id, 
    TH.move_in_date, 
    TH.move_out_date, 
    TH.rent, 
    R.[Total Referral], -- 用方括号包裹带空格的列名
    ES.latest_employer, 
    ES.occupational_category
FROM P
INNER JOIN TH ON P.profile_id = TH.profile_id
INNER JOIN ES ON P.profile_id = ES.profile_id
INNER JOIN R ON P.profile_id = R.profile_id
ORDER BY TH.rent DESC; -- 添加租金降序排序

补充说明 #

如果修正后仍出现相同错误，需检查：

• 数据库是否支持CTE语法（如MySQL 8.0+、SQL Server、PostgreSQL均支持）；
• Profiles$、Tenancy_History等表名是否正确，是否存在拼写错误或权限问题。

内容的提问来源于stack exchange，提问作者Shubham Dandriyal