sql - 计数项目时完全外部加入访问
问题描述
我正在尝试在 Access 中创建完整的外部联接,同时计算项目并获取百分比。
这是我的代码:
SELECT Main.Draft, Count(Main.Draft) AS MainCount, (Count([Main.Draft])/(Select Count(Main.Draft) from Main)) AS Percentage
FROM Main LEFT JOIN Main_ChangeLog ON Main.ID = Main_ChangeLog.ID
GROUP BY Main.Draft;
UNION ALL
SELECT Main.Draft, Count(Main.Draft) AS MainCount, (Count([Main.Draft])/(Select Count(Main.Draft) from Main)) AS Percentage
FROM Main RIGHT JOIN Main_ChangeLog ON Main.ID = Main_ChangeLog.ID
WHERE (((Main.Date) Between [Forms]![Main Reports]![txtbegin] And [Forms]![Main Reports]![txtend]))
GROUP BY Main.Draft;
这是我的结果:
Version_1 1 0
Version_2 1 0
Version_3 3 0
Version_1 4 0
Version_2 3 0
这是我想要的结果:
Version_1 5 0.50
Version_2 4 0.15
Version_3 3 0.35
这是主表:
ID CreateDate FirstName LastName Draft
1 10/01/2020 First One Version_2
2 11/20/2020 Second Person Version_3
3 11/20/2020 Third Girl Version_3
4 11/21/2020 Fourth Boy Version_3
5 11/22/2020 Fifth Guy Version_1
这是 Main_ChangeLog 表:
ID CreateDate FirstName LasteName Draft
1 9/10/2020 First One Version_1
2 9/10/2020 Second Person Version_1
2 10/10/2020 Second Person Version_2
3 9/10/2020 Third Girl Version_1
3 10/10/2020 Third Girl Version_2
4 9/5/2020 Fourth Boy Version_1
4 10/10/2020 Fourth Boy Version_2
每次更新主表上的记录时,前一条记录都存储在 Main_ChangeLog 中。它有助于真正了解每个月创建了多少个版本。
我刚刚将我的代码修改为:
SELECT t.Draft, Count(t.Count) AS DraftCount, (Count(t.Draft)/(Select Count(t.Draft) from Main)) AS [Percentage]
FROM
(SELECT Main.Draft as Draft, Main.Draft as [Count]
FROM MainLEFT JOIN Main_ChangeLog ON Main.ID = Main_ChangeLog.ID
WHERE (((Main.Date) Between [Forms]![Main Reports]![txtbegin] And [Forms]![Main Reports]![txtend]))
UNION ALL
SELECT Main.Draft AS Draft, Main.Draft as [Count]
FROM MainRIGHT JOIN Main_ChangeLog ON Main.ID = Main_ChangeLog.ID
WHERE (((Main.Date) Between [Forms]![Main Reports]![txtbegin] And [Forms]![Main Reports]![txtend]))) AS t
GROUP BY t.Draft
现在我收到此错误:
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
谢谢。
解决方案
这是通过两个查询完成它的一种方法:
- 第一个查询(我们称之为
qryMainData
)将使用以下命令将您的所有版本数据连接在一起UNION ALL
:
SELECT ID, Draft, CreateDate FROM Main
UNION ALL
SELECT ID, Draft, CreateDate FROM Main_ChangeLog;
- 第二个查询将基于第一个查询,因为您想使用查询 1 (
qryMainData
) 的整个结果集的总数来计算百分比:
SELECT Draft, Count(ID) as DraftQty, (SELECT COUNT(ID) FROM qryMainData) as TotalDraftQty,
ROUND(DraftQty/TotalDraftQty, 2) as DraftPercentage
FROM qryMainData
WHERE CreateDate Between [Forms]![Main Reports]![txtbegin] And [Forms]![Main Reports]![txtend]
GROUP BY Draft
MS Access 允许您在计算中使用字段别名。我还使用该ROUND
函数将结果四舍五入到小数点后两位。
推荐阅读
- javascript - 单击按钮后显示连续数字的问题
- c# - SignTool 错误:未找到满足所有给定条件的证书。- 如何建造
- python - How to draw on a Fourier transform numpy array Opencv
- python - 从 csv 文件读取变量时 Subprocess.Popen() 不起作用
- javascript - 在 React 中创建标题
- database-administration - 当我运行维护计划时,日志查看器中显示的用户“sa”登录失败
- django - Django 视图中的动态模板
- ctypes - Ctypes LPSTR 函数参数
- c++ - 测量 C/C++ 代码块中分配的内存
- linux - 如何在bash中将变量分配给同一目录中文件的不同扩展名