sql - 如果表来自同一服务器不同的数据库，为什么会出现错误 'invalid object name'？

Question

通过下面的查询，我正在尝试连接两个表:

select  
    a1.member_account,
    substring(substring(a1.member_name, charindex('/', a1.member_name)  + 1, len(a1.member_name)), 0, charindex(' ', substring(a1.member_name, charindex('/', a1.member_name) + 1, len(a1.member_name)))) as firstname, 
    substring(a1.member_name, 0, charindex('/', a1.member_name)) as lastname,
    a2.account, a2.PriorMonthRewardAmount,
    a2.CurrentMonthRewardAmount, a2.RewardDate, a2.RedemptionAmount, 
    a2.RedemptionDate, a1.member_email, 
    a1.member_rest_flag11, a1.member_rest_flag12
from 
    [NFCUDW].[dbo].[member] as a1
left join 
    [IMS].[dbo].[RewardsHistory] as a2 on a2.account = a1.member_account 
order by 
    DATEPART(year, a2.rewarddate) desc,
    DATEPART(month, a2.rewarddate) desc

这两个表在同一台服务器上，但在不同的数据库中，我正在尝试加入它们。

我得到错误:

Msg 208, Level 16, State 1, Line 1
Invalid object name 'IMS.dbo.RewardsHistory'.

我可以访问这两个表，所以我不明白为什么会出现此错误。

我最初尝试做的是加入这两个查询:

select 
    * 
from 
    RewardsHistory
where 
    priormonthrewardamount > 0
order by 
    DATEPART(year, rewarddate) desc,
    DATEPART(month, rewarddate) desc 

select  
    member_account,
    substring(substring(member_name, charindex('/', member_name) + 1, len(member_name)), 0, charindex(' ', substring(member_name, charindex('/', member_name) + 1, len(member_name)))) as firstname, 
    substring(member_name, 0, charindex('/', member_name)) as lastname,
    member_email, member_rest_flag11, member_rest_flag12
from 
    member

Answer 1

我在这里可能完全错了，只是在黑暗中一枪，但是你声明别名的查询部分

as lastname,a2.account, a2.PriorMonthRewardAmount,
   a2.CurrentMonthRewardAmount,a2.RewardDate,a2.RedemptionAmount,a2.RedemptionDate,
   a1.member_email,a1.member_rest_flag11,a1.member_rest_flag12

好像会出问题。它看起来确实令人困惑，但别名不应该是单个术语或带引号的字符串吗？您已经包含了整个列表。