时间戳

首页 > 解决方案 > 如何在多个字段上使用相同的参数？

ms-access - 如何在多个字段上使用相同的参数？

问题描述

我想在多个字段上使用相同的参数，因为我从 Access 中提取了一半的数据，从 SQL Server 中提取了一半的数据。SQL Server 表主要是 Access 表的克隆。所有新数据都进入 SQL Server，但用户仍然通过 Access（通过 ODBC 数据源）与所有内容进行交互。

我正在尝试使一些旧查询与新的 SQL Server 表无缝协作。基本上它们看起来像这样：

SELECT [AccessCoolUsersTable].Name AS Name, [AccessCoolUsersTable].FavoriteSandwhich AS Sandwhich
FROM [AccessCoolUsersTable]
WHERE [AccessCoolUsersTable].FavoriteSandwhich = [Enter a Sandwhich]

我希望它们像这样运行（这是伪代码）

SELECT [AccessCoolUsersTable].Name As Name, [AccessCoolUsersTable].FavoriteSandwhich AS Sandwhich
FROM [AccessCoolUsersTable]
UNION
SELECT [SQLCoolUsersTable].Name As Name, [SLCoolUsersTable].FavoriteSandwhich AS Sandwhich
FROM [SQLCoolUsersTable]
WHERE 
    [AccessCoolUsersTable].FavoriteSandwhich = [Enter a Sandwhich]
    OR
    [SQLCoolUsersTable].FavoriteSandwhich = [Enter a Sandwhich]

但发生的情况是它首先提示输入“输入参数值[AccessCoolUsersTable].FavoriteSandwhich”，然后提示输入“输入参数值Enter a Sandwhich”。

我只希望它请求一次值并将其用于[AccessCoolUsersTable].FavoriteSandwhich和[SQLCoolUsersTable]。那可能吗？

标签： ms-access

解决方案

您的WHERE子句需要在“UNION”查询中的单个查询上。您还需要在查询中定义参数。

PARAMETERS [Enter a Sandwhich] Text ( 255 );
SELECT [AccessCoolUsersTable].Name As Name, [AccessCoolUsersTable].FavoriteSandwhich AS Sandwhich
FROM [AccessCoolUsersTable]
WHERE [AccessCoolUsersTable].FavoriteSandwhich = [Enter a Sandwhich]
UNION
SELECT [SQLCoolUsersTable].Name As Name, [SLCoolUsersTable].FavoriteSandwhich AS Sandwhich
FROM [SQLCoolUsersTable]
WHERE [SQLCoolUsersTable].FavoriteSandwhich = [Enter a Sandwhich]

或使用联合作为子查询

PARAMETERS [Enter a Sandwhich] Text ( 255 );
SELECT a.Name, a.FavoriteSandwhich FROM
(SELECT [AccessCoolUsersTable].Name As Name, [AccessCoolUsersTable].FavoriteSandwhich AS Sandwhich
FROM [AccessCoolUsersTable]
UNION
SELECT [SQLCoolUsersTable].Name As Name, [SLCoolUsersTable].FavoriteSandwhich AS Sandwhich
FROM [SQLCoolUsersTable]) 
AS a
WHERE a.FavoriteSandwhich = [Enter a Sandwhich]

第一个查询将具有更好的性能，因为结果首先被过滤，WHERE然后添加到UNION

推荐阅读