在Access SQL查询中使用count()时遇到问题 [英] Having trouble using count() in Access SQL query

问题描述

使用Access 2007.

Using Access 2007.

我有一个包含以下字段的表清单:

I have a table Inventory with the following fields:

容器ID 文件夹ID

ContainerID FolderID

我的目标是找出哪些FolderID与多个ContainerID对应，以及这些记录的ContainerID是什么.我认为处理此问题的最佳方法是执行一个查询，该查询返回ContainerID，FolderID和每个FolderID的计数，以便我可以对该列表进行排序，以将count> 1的记录放在顶部.我尝试过:

My goal is to find out which FolderIDs correspond to more than one ContainerID, and what the ContainerIDs are for these records. I thought the best way to handle this would be doing a query that returns ContainerID, FolderID, and a count of each FolderID, so that I could sort that list to put the records with count>1 on top. I tried this with:

select ContainerID,FolderID,count(FolderID) from (select distinct * from Inventory);

但是Access给我一个错误消息:您试图执行一个不包含指定表达式'ContainerID'作为聚合函数一部分的查询."

but Access gave me an error message: "You tried to execute a query that does not include the specified expression 'ContainerID' as part of an aggregate function."

如何获得所需的结果?

我正在尝试所有的解决方案，但是所有这些解决方案都在不断出现大量重复的行.我只是试图用DISTINCT过滤掉它们，但是由于某些原因冻结了Access.我必须回家，明天早上再检查一下.感谢您提供建议.

I'm trying all of your solutions but all of them keep getting a lot of duplicate rows. I just tried to filter them out with DISTINCT but for some reason that froze up Access. I have to go home so I'll check these out again tomorrow morning. Thanks for offering suggestions.

推荐答案

尝试以下查询.

select
     I.*
    ,T.FolderCount
from
    Inventory I
inner join
(
    select
          ContainerID
        , Count(*) as FolderCount
    from
        Inventory
    group by
        ContainerID
    having
        Count(*) > 1
) T
on
    I.ContainerID = T.ContainerID

select
     I.*
    ,T.ContainerCount
from
    Inventory I
inner join
(
    select
          FolderID
        , Count(*) as ContainerCount
    from
        Inventory
    group by
        FolderID
    having
        Count(*) > 1
) T
on
    I.FolderID = T.FolderID

这篇关于在Access SQL查询中使用count()时遇到问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！