时间戳

首页 > 解决方案 > 无法在 MS Access 中跨联合分配别名

sql - 无法在 MS Access 中跨联合分配别名

问题描述

我正在尝试从我的数据库中的表创建一个邮件列表，然后在该表中搜索包含兄弟姐妹信息的列，提取该兄弟姐妹信息，在表中搜索这些兄弟姐妹，然后将这些兄弟姐妹加入原始邮件列表。

在下面的代码中，我尝试将别名 t1 分配给选择查询。

SELECT * FROM CountyData AS t1
WHERE dt_dec BETWEEN #05-6-2018# AND #06-06-2018# 
AND RippleImport IS NULL 
AND (Date()-[dob])/365.25 <13.875 

UNION SELECT CountyData.*
FROM CountyData INNER JOIN 

(SELECT TRIM(String_Split([Siblings], 0, ";")) FROM t1 WHERE Siblings IS NOT NULL
UNION
SELECT TRIM(String_Split([Siblings], 1, ";")) FROM t1 WHERE Siblings IS NOT NULL 
UNION
SELECT TRIM(String_Split([Siblings], 2, ";")) FROM t1 WHERE Siblings IS NOT NULL
UNION 
SELECT TRIM(String_Split([Siblings], 3, ";")) FROM t1 WHERE Siblings IS NOT NULL) t2

ON CountyData.[fname]&" "&[lname] = t2.[Expr1000] WHERE RippleImport IS NULL;

当我尝试在第一个联合的另一侧调用该别名时，我收到以下错误消息。 

'Microsoft Access database engine cannot find the input table or query 't1'. Make sure it exists and that its name is spelled correctly.'

如何分配将在整个查询中识别的表别名？

标签： sqlms-accessalias

解决方案

以下应该更正代码中别名的使用：

select t1.* from countydata t1
where t1.dt_dec between #05-6-2018# and #06-06-2018# 
and t1.rippleimport is null 
and (date()-t1.dob)/365.25 <13.875 
union
select t1.* from countydata t1 inner join 
(
    select trim(string_split(t1.siblings, 0, ";")) as sib from countydata t1 where t1.siblings is not null
    union
    select trim(string_split(t1.siblings, 1, ";")) as sib from countydata t1 where t1.siblings is not null 
    union
    select trim(string_split(t1.siblings, 2, ";")) as sib from countydata t1 where t1.siblings is not null
    union 
    select trim(string_split(t1.siblings, 3, ";")) as sib from countydata t1 where t1.siblings is not null
) t2 on t1.fname & " " & t1.lname = t2.sib 
where t1.rippleimport is null;

但是，使用计算的连接标准可能更有效，例如：

select t1.* from countydata t1
where t1.dt_dec between #05-6-2018# and #06-06-2018# 
and t1.rippleimport is null 
and (date()-t1.dob)/365.25 <13.875 
union
select t1.* 
from countydata t1 inner join 
(
    select t.siblings as s from countydata t where t.siblings is not null
) 
t2 on 
(
    t1.fname & " " & t1.lname = trim(string_split(t2.s, 0, ";")) or
    t1.fname & " " & t1.lname = trim(string_split(t2.s, 1, ";")) or
    t1.fname & " " & t1.lname = trim(string_split(t2.s, 2, ";")) or
    t1.fname & " " & t1.lname = trim(string_split(t2.s, 3, ";"))
)
where t1.rippleimport is null;

以上是完全未经测试的。

推荐阅读