跨多个表聚合SQL查询 [英] Aggregate SQL query across multiple tables

问题描述

我正在使用MS Access2010.该数据库具有一组12个相同的设备表，每个表分别用于公司的12个不​​同部门.该表跟踪负责设备的人员(资源)需要采取的行动.我有一个简单的查询，该查询计算具有各种状态的资源的数量.它看起来如下:

I'm using MS Access 2010. The database has a set of 12 identical equipment tables, one for each of 12 different departments in a company. The table tracks actions that need to be taken by folks (resources) who are responsible for the equipment. I have a simple query that counts the number of resources that have various statuses. It looks as follows:

SELECT dept1.actions.resource, dept1.action.status, Count(*) AS status_count
FROM dept1.action
GROUP BY dept1.action.status, dept1.action.resource;

每个表如下所示:

equip_id, text
resource, number (id of the resource who is responsible for the equipment)
status, number (id of the status of the action that the resource needs to do)

查询结果如下:

resource  status  status_count
1         1       63
2         1       79
5         1       16
6         1       3
0         3       1
1         3       1180
2         3       64
3         3       61
5         3       1
6         3       2
7         3       12
0         4       4

例如，第一行显示资源1具有状态为1的63台设备.第二行显示资源2具有状态1 ...的79台设备.依此类推.

For example, the first row shows that resource 1 has 63 pieces of equipment that are of status 1. The second row shows that resource 2 has 79 pieces of equipment of status 1... and so on.

我需要的是一个聚合查询，该查询可为所有资源和状态组合(即完全相同的结果表)提供公司级别的总计，但status_count列的数字将大得多.

What I need is an aggregate query that provides company-level totals for all of the resource and status combinations, i.e., the exact same result table, except that the status_count column will have significantly larger numbers.

感谢您提供的任何帮助.

Thank you for any help you can provide.

推荐答案

基本上，您可以为数据库中的每个dep表选择计数，将它们合并，然后求和.

Basically, you can select the count for each dep table in your database , Union them all and then sum them.

SELECT resource,status,sum(status_count) as status_company_count from(
SELECT dept1.actions.resource, dept1.action.status, Count(*) AS status_count
FROM dept1.action
GROUP BY dept1.action.status, dept1.action.resource
UNION
SELECT dept2.actions.resource, dept2.action.status, Count(*) AS status_count
FROM dept2.action
GROUP BY dept2.action.status, dept2.action.resource
UNION
SELECT dept3.actions.resource, dept3.action.status, Count(*) AS status_count
FROM dept3.action
GROUP BY dept3.action.status, dept3.action.resource
.....)
group by resource,status;

这篇关于跨多个表聚合SQL查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！