如何在Access查询结果中包括空行 [英] How to include empty rows in Access query results

问题描述

我有一个包含客户端信息的Access 2010数据库.我需要创建一个表，列出每个年龄段的客户数量.我要向其报告的代理商想要一份报告，其中列出了0到100岁之间每个年龄段的客户数量.下面的SQL查询将创建所需的报告，但不包括客户端数为零的年龄.

I have an Access 2010 database with client information. I need to create a table of the number of clients in each age. The agency I am reporting to wants a report with the number of clients of every age from 0 - 100 years listed. The SQL query below will create the required report, but does not include ages with zero clients.

SELECT AgeNum & " years" AS [Age], Count(*) AS [Count]
FROM (SELECT Int(DateDiff("d", Clients.dob, now())/365.25) AS AgeNum 
FROM Clients)  AS [%$##@_Alias]
GROUP BY [%$##@_Alias].AgeNum;

如何让查询返回计数"列中带有0的空行?

How can I have the query return the empty rows with 0 in the Count column?

我环顾四周，发现了这一点

I looked around and found this:

它们创建一个值表来查找空组.它与我所需要的非常相似，只是它使用了Access 2010不支持的Coalesce函数.

They create a table of values to lookup the empty groups. It is very similar to what I need except it uses a Coalesce function which is not supported in Access 2010.

推荐答案

系统仅知道存在特定记录的年龄.如果要列出1..100岁之间的年龄，则需要告知或提供您要寻找0..100岁的年龄的系统.通过提供您要查找的年龄列表，如果在搜索记录中找不到所需的年龄，系统将自动返回0/null.

The system only knows there is an age if a particular record exists. If you want to have a list of age between 1..100, you need to tell or provide the system that you are looking for 0..100 ages. By providing a list of ages you are looking for, system will automatically return 0/null if the requested age is not found withing your searched records.

就像其他人提到的那样，您可以有一个以1..100作为行的表，并在SQL中对其进行比较，或者可以使用SQL生成数字列表.

As others mentioned, you can have a table with 1..100 as rows and compare them in your SQL or you could generate list of numbers with SQL.

某些DBMS提供了一个称为double的默认表，该表具有一列和一行，您可以将该表用于没有from表的任何查询. 在访问应用程序中，创建一个名为"dual"的表并插入一行.

Some DBMS provide a default table called dual which has one column and one row, you can use that table for any queries that does not have a from table. In your access application, create a table called "dual" and insert one row.

现在执行以下查询:

SELECT TMain.counter
FROM (SELECT (T2.mAge*t3.mFactor10)+t1.mAge AS counter
FROM (select 1 as mAge from dual
union all select 2 from dual
union all select 3 from dual
union all select 4 from dual
union all select 5 from dual
union all select 6 from dual
union all select 7 from dual
union all select 8 from dual
union all select 9 from dual
union all select 10 from dual)  AS T1, 
(select 0 as mAge from dual
union all select 1 from dual
union all select 2 from dual
union all select 3 from dual
union all select 4 from dual
union all select 5 from dual
union all select 6 from dual
union all select 7 from dual
union all select 8 from dual
union all select 9 from dual
union all select 10 from dual)  AS T2,
(select 10 as mFactor10 from dual) AS T3 )  AS TMain
WHERE (((TMain.counter) Between 1 And 100));

这将从1..100开始产生100行.

this will produce 100 rows from 1..100.

然后您可以将此结果用作SQL的外部表，并查找/计算年龄在此列表中的任何人. 逻辑将是:

you can then use this result as outer table for your SQL and find/count anyone whose age is on this list. the logic would be:

select all age 
from the reqeusted age list
find  and count/return all matched records or return 0 if no records found.

在SQL中，将是这样，

In SQL, it would be something like this,

SELECT TMain.counter as Age,
(SELECT Count(*) AS [Count]
FROM (SELECT Int(DateDiff("d", Clients.dob, now())/365.25) AS AgeNum 
FROM Clients)  AS [%$##@_Alias]
WHERE (TMain.counter = [%$##@_Alias].ageNum)
GROUP BY [%$##@_Alias].AgeNum) as number_of_clients
FROM (SELECT (T2.mAge*t3.mFactor10)+t1.mAge AS counter
FROM (select 1 as mAge from dual
union all select 2 from dual
union all select 3 from dual
union all select 4 from dual
union all select 5 from dual
union all select 6 from dual
union all select 7 from dual
union all select 8 from dual
union all select 9 from dual
union all select 10 from dual)  AS T1, 
(select 0 as mAge from dual
union all select 1 from dual
union all select 2 from dual
union all select 3 from dual
union all select 4 from dual
union all select 5 from dual
union all select 6 from dual
union all select 7 from dual
union all select 8 from dual
union all select 9 from dual
union all select 10 from dual)  AS T2,
(select 10 as mFactor10 from dual) AS T3 )  AS TMain
WHERE (((TMain.counter) Between 1 And 100));

这将产生:年龄从1..100开始，以及每个年龄段的客户数量，而null表示null/empty没有结果. 当然，您可以动态地延长或缩短年龄列表.

this will produce: age from 1..100 as well as number of clients for each age and null for null/empty no results . of course, you can dynamically extend or shorten the age list.

这篇关于如何在Access查询结果中包括空行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！