访问 - 根据不同的列值获取不同的行数 [英] Access - Get distinct count of rows based on different column values

问题描述

我有一个格式如下的访问数据库:

I have an access database with the following format:

ID  | Period  | Data
---------------------
13  | 1       | xxxxx
13  | 2       | xxxxx
25  | 1       | xxxxx
25  | 2       | xxxxx
25  | 3       | xxxxx
52  | 1       | xxxxx
52  | 3       | xxxxx
94  | 1       | xxxxx
94  | 2       | xxxxx
94  | 3       | xxxxx

周期可以是 1、2 或 3.我需要获取周期为 1、2 和 3，或周期为 1 和 2，或周期为 1 和 3 的 ID 的计数(它们总是有周期1).我如何运行这样的查询 - 它可以是 3 个不同的查询来达到不同的排列.结果如下:

The Period can be 1, 2, or 3. I need to get a count of the IDs that have a period of 1, 2, and 3, or periods 1 and 2, or 1 and 3 (they always have period 1). How can I run such a query - it can be 3 different queries to hit the different permutations. The results would be like the following:

第 1、2 和 3 期:计数:2(对于 ID 25 和 94，虽然我不一定需要返回 ID，只需要计数)

Periods 1, 2, and 3: Count: 2 (for IDs 25 and 94, although I don't necessarily need the IDs returned, just the count)

第 1 和第 2 期:计数:1 (ID 13)

第 1 和第 3 期:计数:1 (ID 52)

这可以通过 Access 实现吗?它不是完全不同的值，所以这个问题和链接文章 似乎没有提供足够的帮助.我还没有数据库，这就是我无法测试该查询的原因，但我想知道在创建表之前是否可行，或者我是否需要找到其他解决方案.

Is this possible with Access? It's not quite distinct values, so this question and the linked article don't seem to provide enough help. I don't have the database yet which is why I can't test that query, but I want to know if it would be possible before creating the table or if I need to find another solution.

推荐答案

像这样创建转换查询并保存它的最简单方法

The easiest way to create a transform query like this and save it

TRANSFORM Count(YourTable.ID) AS CountOfID
SELECT YourTable.ID
FROM YourTable
GROUP BY YourTable.ID
PIVOT YourTable.Period)

哪些输出

ID  1 2 3
--  - - -
13  1 1 
25  1 1 1
52  1   1
94  1 1 1

然后您可以使用它创建另一个查询 Lke this

You can then use that create another query Lke this

SELECT t.Groups, Count(t.ID) AS CountOfID
FROM (SELECT ID, 
     SWITCH(
               [1] = 1 and [2] =1 and [3] = 1, 'Periods 1, 2, and 3',
               [1] = 1 and [2] =1 , 'Periods 1 and 2',
               [1] = 1 and [3] =1 , 'Periods 1 and 3'
               )  as Groups

FROM TheSavedTransformQuery)  AS t
GROUP BY t.Groups;

输出这个

Groups              CountOfID
------------------- ---------
Periods 1 and 2        1
Periods 1 and 3        1
Periods 1, 2, and 3    2

如果你想把它作为单个 SQL 语句来做，这也行

If you want to do it as single SQL statement this will work as well

SELECT 
    t.Groups, 
    Count(t.ID) AS CountOfID
FROM (

    SELECT 
        Switch(
            FirstPeriod.Period=1 And [SecondPeriod.Period ]=2 And [ThirdPeriod.Period]=3,'Periods 1, 2, and 3',
            FirstPeriod.Period=1 And [SecondPeriod.Period]=2,'Periods 1 and 2',
            FirstPeriod.Period=1 And [ThirdPeriod.Period]=3,'Periods 1 and 3') AS Groups, 
            DistinctID.ID
    FROM (((SELECT DISTINCT ID
        FROM 
        YourTable )  AS DistinctID 

        LEFT JOIN (SELECT ID, PERIOD 
        FROM YourTable
        WHERE Period = 1)  AS FirstPeriod 

        ON DistinctID.ID = FirstPeriod.ID) 

        LEFT JOIN (SELECT ID, PERIOD 
        FROM YourTable  WHERE Period = 2)  AS SecondPeriod 
        ON DistinctID.ID = SecondPeriod.ID) 

        LEFT JOIN (SELECT ID, PERIOD 
        FROM YourTable
        WHERE Period = 3)  AS ThirdPeriod 
        ON DistinctID.ID = ThirdPeriod.ID

)AS t
GROUP BY 
    t.Groups

这篇关于访问 - 根据不同的列值获取不同的行数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！