多条件的MS Access查询

Question

如何在MS Access中构建以下查询?

在我的结果表中有以下值:

当专栏1:

• 三行包含相同的数值&;####&;，但&;####&;必须包含所有3个扩展名">.par"， ">.dft"， "*.psm">

AND Column 2

• 示例中的第一行必须与第二行
中的值相同。

AND Column 3

• 示例中的第一行必须与第二行
中的值相同。

<表类> 第1列 第2列 第三列 tbody><<tr>1234. par1234011234. dft1234011234. psm123401

必须使用自连接来比较同一表的行。这将产生属于一个行的3行。

SELECT t1.Column1, t2.Column1, t3.Column1, t1.Column2, t1.Column3
FROM
(T AS t1
INNER JOIN T AS t2 ON (t1.Column3 = t2.Column3) AND (t1.Column2 = t2.Column2))
INNER JOIN T AS t3 ON (t2.Column3 = t3.Column3) AND (t2.Column2 = t3.Column2)
WHERE
t1.Column1=[t1].[Column2] & '.par' AND
t2.Column1=[t2].[Column2] & '.dft' AND
t3.Column1=[t3].[Column2] & '.psm';

如果你需要它们作为三行，你有不同的可能性。一种方法是在UNION ALL查询中使用该查询3次，每次从不同的表中选择列:

SELECT t1.Column1, t1.Column2, t1.Column3
FROM
(T AS t1
INNER JOIN T AS t2 ON (t1.Column3 = t2.Column3) AND (t1.Column2 = t2.Column2))
INNER JOIN T AS t3 ON (t2.Column3 = t3.Column3) AND (t2.Column2 = t3.Column2)
WHERE
t1.Column1=[t1].[Column2] & '.par' AND
t2.Column1=[t2].[Column2] & '.dft' AND
t3.Column1=[t3].[Column2] & '.psm'
UNION ALL
SELECT t2.Column1, t2.Column2, t2.Column3
FROM
(T AS t1
INNER JOIN T AS t2 ON (t1.Column3 = t2.Column3) AND (t1.Column2 = t2.Column2))
INNER JOIN T AS t3 ON (t2.Column3 = t3.Column3) AND (t2.Column2 = t3.Column2)
WHERE
t1.Column1=[t1].[Column2] & '.par' AND
t2.Column1=[t2].[Column2] & '.dft' AND
t3.Column1=[t3].[Column2] & '.psm'
UNION ALL
SELECT t3.Column1, t3.Column2, t3.Column3
FROM
(T AS t1
INNER JOIN T AS t2 ON (t1.Column3 = t2.Column3) AND (t1.Column2 = t2.Column2))
INNER JOIN T AS t3 ON (t2.Column3 = t3.Column3) AND (t2.Column2 = t3.Column2)
WHERE
t1.Column1=[t1].[Column2] & '.par' AND
t2.Column1=[t2].[Column2] & '.dft' AND
t3.Column1=[t3].[Column2] & '.psm'

另一种可能性是使用第一个查询作为子选择，并再次将其连接到表本身:

SELECT T.*
FROM
(SELECT t1.Column1 as C1, t2.Column1 as C2, t3.Column1 as C3, t1.Column2, t1.Column3
FROM
(T AS t1
INNER JOIN T AS t2 ON (t1.Column3 = t2.Column3) AND (t1.Column2 = t2.Column2))
INNER JOIN T AS t3 ON (t2.Column3 = t3.Column3) AND (t2.Column2 = t3.Column2)
WHERE
t1.Column1=[t1].[Column2] & '.par' AND
t2.Column1=[t2].[Column2] & '.dft' AND
t3.Column1=[t3].[Column2] & '.psm') AS X
INNER JOIN T ON (X.C1 = T.Column1 OR X.C2 = T.Column1 OR X.C3 = T.Column1);

注意，最后两个示例不能在可视化查询设计器中显示。必须使用设计器的SQL视图。此外，可视化查询设计器往往会变形最后一个查询，使其无法使用。