加快Access 2016查询

debugcn 发表于 Dev

抢

我有一个查询，其中包含batsmanID和联赛名称（摘录如下）。我将另一个查询放在一起，以返回击球手在两个联赛中都打过的所有记录。

该查询有效，但是它非常非常慢。

在第一个查询中返回了48,000条记录，但是当我使用它时，它会立即运行。

谁能建议如何加快新查询的速度？

谢谢

查询：

SELECT *
FROM AddDateLeagueSeasontoScorecardBatting
WHERE League ="Indian Premier League" AND batsmanId IN (SELECT batsmanId FROM AddDateLeagueSeasontoScorecardBatting WHERE League ="Big Bash League") OR League ="Big Bash League" AND batsmanId IN (SELECT batsmanId FROM AddDateLeagueSeasontoScorecardBatting WHERE League ="Indian Premier League");

表：

李·麦克

您可以使用inner joins以下命令来完成此操作：查询将仅返回两个子查询中都出现蝙蝠侠的那些记录：

select t1.* from 
(
    AddDateLeagueSeasontoScorecardBatting t1 inner join
    (
        select distinct batsmanid 
        from AddDateLeagueSeasontoScorecardBatting 
        where League ="Indian Premier League"
    ) t2 on t1.batsmanid = t2.batsmanid
) 
inner join
(
    select distinct batsmanid 
    from AddDateLeagueSeasontoScorecardBatting 
    where League ="Big Bash League"
) t3 on t1.batsmanid = t3.batsmanid

编辑：要选择仅在有问题的两个联赛中踢球的蝙蝠侠（没有其他人），您可以尝试：

select t1.* from 
AddDateLeagueSeasontoScorecardBatting t1 inner join
(
    select batsmanid
    from AddDateLeagueSeasontoScorecardBatting 
    group by batsmanid
    having 
        sum(League not in ("Indian Premier League", "Big Bash League")) = 0 and
        sum(League = "Indian Premier League") < 0 and 
        sum(League = "Big Bash League") < 0
) t2 on t1.batsmanid = t2.batsmanid

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。