mysql - MySQL连接三张表获取汇总数据

Question

我在 SQL 方面是新手，我有一个问题，我相信可以通过一些 SQL 查询来解决......如果我更好地理解连接的话。

我有三个表，每个表都通过主键/外键链接到另一个表。事实上，最深的表中大约有 60,000 条记录，但为简单起见，您可以使用以下内容重新创建我的表结构:

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)

基本上，第一个表是一个文件列表，每个文件都有一个与之关联的分析师。每位分析师可以有多个文件。

fd_ID_pk   Analyst
1         AD
2         LS
3         MM
4         MM
etc

第二个表是该文件中的数据条目列表，每条记录都有一个键。

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

第三张表对我来说比较复杂。这有三列，每列都链接到第二个表中的一条记录，该记录可以为空

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。

我得到了这个，它给出了每个分析师的总计数，但它们似乎根本不对:

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

然而，理想情况下，我正在努力做到这一点:

Analyst   TotalFiles   FilesUsed   TotalInd   IndUsed
AD        2            2           14         10        
LS        3            3           24         16
MM        4            4           32         24

有一些疑问......任何建议将不胜感激!

Answer 1

首先尝试将表 3 和表 2 连接起来

SELECT ID.sp_ID_pk, COUNT(*) AS VAL , COUNT(distinct IndNum) AS IDUSED
  FROM IndData ID 
  LEFT JOIN FinData FD ON FD.sp_1_fk = ID.sp_ID_pk 
  LEFT JOIN FinData FDD ON FDD.sp_2_fk = ID.sp_ID_pk 
  LEFT JOIN FinData FDDD ON FDDD.sp_3_f k= ID.sp_ID_pk 
GROUP BY ID.sp_ID_pk;



 +----------+-----+--------+
    | sp_ID_pk | VAL | IDUSED |
    +----------+-----+--------+
    |        1 |   1 |      1 |
    |        2 |   1 |      1 |
    |        3 |   1 |      1 |
    |        4 |   1 |      1 |
    |        5 |   1 |      1 |
    |        6 |   2 |      1 |
    |        7 |   1 |      1 |
    |        8 |   1 |      1 |
    |        9 |   1 |      1 |
    |       10 |   1 |      1 |
    |       11 |   2 |      1 |
    |       12 |   2 |      1 |
    |       13 |   2 |      1 |
    |       14 |   1 |      1 |
    |       15 |   1 |      1 |
    |       16 |   1 |      1 |
    |       17 |   2 |      1 |
    |       18 |   2 |      1 |
    |       19 |   1 |      1 |
    |       20 |   1 |      1 |
    |       21 |   1 |      1 |
    |       22 |   1 |      1 |
    |       23 |   1 |      1 |
    |       24 |   1 |      1 |
    |       25 |   1 |      1 |
    |       26 |   2 |      1 |
    |       27 |   1 |      1 |
    |       28 |   1 |      1 |
    |       29 |   1 |      1 |
    |       30 |   1 |      1 |
    |       31 |   1 |      1 |
    |       32 |   1 |      1 |
    |       33 |   1 |      1 |
    |       34 |   1 |      1 |
    |       35 |   2 |      1 |
    |       36 |   1 |      1 |
    |       37 |   1 |      1 |
    |       38 |   1 |      1 |
    |       39 |   1 |      1 |
    |       40 |   1 |      1 |
    |       41 |   1 |      1 |
    |       42 |   1 |      1 |
    |       43 |   1 |      1 |
    |       44 |   1 |      1 |
    |       45 |   1 |      1 |
    |       46 |   2 |      1 |
    |       47 |   1 |      1 |
    |       48 |   1 |      1 |
    |       49 |   1 |      1 |
    |       50 |   1 |      1 |
    |       51 |   1 |      1 |
    |       52 |   1 |      1 |
    |       53 |   2 |      1 |
    |       54 |   1 |      1 |
    |       55 |   1 |      1 |
    |       56 |   1 |      1 |
    |       57 |   1 |      1 |
    |       58 |   2 |      1 |
    |       59 |   1 |      1 |
    |       60 |   1 |      1 |
    |       61 |   1 |      1 |
    |       62 |   1 |      1 |
    |       63 |   1 |      1 |
    |       64 |   2 |      1 |
    |       65 |   1 |      1 |
    |       66 |   1 |      1 |
    |       67 |   2 |      1 |
    |       68 |   1 |      1 |
    |       69 |   1 |      1 |
    |       70 |   1 |      1 |
    +----------+-----+--------+
    70 rows in set (0.01 sec)

然后加入下表

mysql> select * from FileData;
+----------+---------+
| fd_ID_pk | Analyst |
+----------+---------+
|        1 | AD      |
|        2 | LS      |
|        3 | MM      |
|        4 | MM      |
|        5 | MM      |
|        6 | LS      |
|        7 | LS      |
|        8 | AD      |
|        9 | MM      |
+----------+---------+
9 rows in set (0.00 sec)

你会得到你想要的