连接三张表获取MySQL中的汇总数据 [英] Joining three tables to get summary data in MySQL
问题描述
我是 SQL 新手，并且有一个问题，我相信可以通过几个 SQL 查询来解决这个问题……如果我更了解联接的话.
I'm new-moderate at SQL and have an issue which I believe can be done with a few SQL queries ... if I understood Joins better.
我有三个表，每个表都通过主键/外键链接到另一个表.实际情况是，最深的表中大约有 60,000 条记录，但为简单起见，您可以使用以下内容重新创建我的表结构:
I have three tables, each linked via primary/foreign keys to the other. The reality is there are about 60,000 records in the deepest table, but for simplicity you can recreate my table structure with this:
CREATE DATABASE IF NOT EXISTS test;
USE test;
CREATE TABLE IF NOT EXISTS FileData (fd_ID_pk INT NOT NULL AUTO_INCREMENT PRIMARY KEY, Analyst VARCHAR(2) NOT NULL);
INSERT INTO FileData (Analyst) VALUES('AD'), ('LS'), ('MM'), ('MM'), ('MM'), ('LS'), ('LS'), ('AD'), ('MM');
CREATE TABLE IF NOT EXISTS IndData (sp_ID_pk INT NOT NULL AUTO_INCREMENT PRIMARY KEY, fd_ID_fk INT NOT NULL, IndNum INT NOT NULL, FOREIGN KEY (fd_ID_fk) REFERENCES FileData (fd_ID_pk));
INSERT INTO IndData (fd_ID_fk, IndNum) VALUES (1,1), (1,2), (1,3), (1,4), (1,5), (1,6), (2,1), (2,2), (2,3), (2,4), (2,5), (2,6), (2,7), (2,8), (2,9), (2,10), (3,1), (3,2), (3,3), (3,4), (3,5), (3,6), (3,7), (3,8), (3,9), (3,10), (3,11), (3,12), (3,13), (3,14), (3,15), (4,1), (4,2), (4,3), (4,4), (4,5), (4,6), (4,7), (4,8), (5,1), (5,2), (5,3), (5,4), (6,1), (6,2), (6,3), (6,4), (6,5), (6,6), (6,7), (7,1), (7,2), (7,3), (7,4), (7,5), (7,6), (7,7), (8,1), (8,2), (8,3), (8,4), (8,5), (8,6), (8,7), (8,8), (9,1), (9,2), (9,3), (9,4), (9,5);
CREATE TABLE IF NOT EXISTS FinData (sp_1_fk INT NULL, sp_2_fk INT NULL, sp_3_fk INT NULL, FOREIGN KEY (sp_1_fk) REFERENCES IndData (sp_ID_pk), FOREIGN KEY (sp_2_fk) REFERENCES IndData (sp_ID_pk), FOREIGN KEY (sp_3_fk) REFERENCES IndData (sp_ID_pk));
INSERT INTO FinData (sp_1_fk, sp_2_fk, sp_3_fk) VALUES (57,null,64), (18,64,67), (null,11,35), (null,58,35), (null,null,24), (18,null,null), (null, 6,26), (34,null, 8), (null, 8,null), (59,68,28), (null, 1,17), (39,55,null), (65,58, 7), (null,null,10), (54, 6,null), (53,null,67), (27,19,41), (null,57, 5), ( 6,31,17),( 4,64,25), (38,13,58), (55,null, 2), (66,null, 4), (10,10,null), (40,61,46), (null,null,52), ( null,39,46), (null,11,32), (12,null,39), (56,44,21),(22,25,53), (37,null,null), (12,null,49), (43, null,13), (19,17,26), (46, 9,44), (null,13,null), (53,null, 6), (32,30,null)
基本上，第一个表是一个文件列表，每个文件都有一个与之关联的分析员.每个分析师可以有多个文件.
Basically, the first table is a list of files and each file has an analyst associated with it. There can be more than one file per analyst.
fd_ID_pk Analyst
1 AD
2 LS
3 MM
4 MM
etc
第二个表是该文件中数据条目的列表，每条记录都有一个键.
The second table is a list of data entries from that file, each record has a key.
sp_ID_pk fd_ID_fk IndNum
1 1 1
2 1 2
3 1 3
4 1 4
5 1 5
6 1 6
7 2 1
8 2 2
etc
第三张表对我来说变得复杂了.这有三列，每列都链接到第二个表中的一条记录，该记录可以为空
The third table is where it gets complicated for me. This has three columns each of which is linked to a record in the second table, which can be null
sp_1_fk sp_2_fk sp_3_fk
12 39
56 44 21
22 25 53
37
12 49
43 13
19 17 26
我需要的是一个汇总表，显示每个分析师的文件计数以及 IndData 和 FinData.
What I need is a summary table that shows the counts of files and IndData and FinData for each analyst.
我得到了这个，它给了我每个分析师的总数，但它们似乎根本不正确:
I've gotten this which gives me the total counts per analyst, but they don't seem right at all:
SELECT filedata.Analyst, COUNT(filedata.Analyst) as 'count'
FROM filedata
JOIN inddata
ON filedata.fd_ID_pk = inddata.fd_ID_fk
JOIN findata
ON findata.sp_1_fk = inddata.sp_ID_pk OR findata.sp_2_fk = inddata.sp_ID_pk OR findata.sp_3_fk = inddata.sp_ID_pk
GROUP BY filedata.Analyst
然而，理想情况下，我试图得到这个:
Ideally, however, I'm trying to get this:
Analyst TotalFiles FilesUsed TotalInd IndUsed
AD 2 2 14 10
LS 3 3 24 16
MM 4 4 32 24
有一些疑问......任何建议将不胜感激！
with some queries ... any advice would be appreciated!
推荐答案
每当查询中涉及多个 1 对多关系时，大多数聚合都需要在子查询中计算，以防止不同的多"乘以预聚合结果计数.
Whenever multiple 1-to-many relations are involved in a query, most aggregates will need calculated in subqueries to prevent the different "many"'s from multiplying the pre-aggregated result count.
这篇关于连接三张表获取MySQL中的汇总数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
