访问 - 根据不同的列值获取不同的行数 [英] 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屋!