如何将3个表连接成一个表，这些表的外键指向第四个表？

配套元件

+------------------+--------------+------+-----+---------+----------------+
| Field            | Type         | Null | Key | Default | Extra          +------------------+--------------+------+-----+---------+----------------+
| id               | int(11)      | NO   | PRI | NULL    | auto_increment | protocol         | varchar(100) | YES  | UNI | NULL    |                | detail_service   | varchar(120) | YES  |     | NULL    |                | date_conclusion  | date         | YES  |     | NULL    |                | resp_key         | varchar(10)  | YES  | MUL | NULL    |                |------------------+--------------+------+-----+---------+----------------+

博士

+---------------------+-------------+------+-----+---------+----------------+
| Field               | Type        | Null | Key | Default | Extra          |
+---------------------+-------------+------+-----+---------+----------------+
| id                  | int(11)     | NO   | PRI | NULL    |auto_increment | employee_key        | varchar(10) | YES  | MUL | NULL    |                | doc_total           | int(11)     | YES  |     | NULL    |                | date_doc            | date        | YES  |     | NULL    |                +---------------------+-------------+------+-----+---------+----------------+

srv公司

+-------------------+--------------+------+-----+---------+----------------+
| Field             | Type         | Null | Key | Default | Extra          +-------------------+--------------+------+-----+---------+----------------+
| id                | int(11)      | NO   | PRI | NULL    | auto_increment | employee_key      | varchar(10)  | YES  | MUL | NULL    |                | srv_total         | int(11)      | YES  |     | NULL    |                | date_srv          | date         | YES  |     | NULL    |                +-------------------+--------------+------+-----+---------+----------------+

钥匙

+-------------------+--------------+------+-----+---------+-------+
| Field             | Type         | Null | Key | Default | Extra |
+-------------------+--------------+------+-----+---------+-------+
| key               | varchar(10)  | NO   | PRI | NULL    |       |
| login_employee    | varchar(100) | YES  | MUL | NULL    |       |
+-------------------+--------------+------+-----+---------+-------+

雇员

+------------+--------------+------+-----+---------+-------+
| Field      | Type         | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| login      | varchar(100) | NO   | PRI | NULL    |       |
| name       | varchar(100) | YES  |     | NULL    |       |
+------------+--------------+------+-----+---------+-------+

我有这5张table。我想要这样的结果：

employee.login, employee.name, SUM(kit.detail_service LIKE '%Deal%') AS 'Deal', SUM(kit.detail_service LIKE '%Not Deal%') AS 'Not Deal', SUM(kit.detail_service LIKE '%Postponed%') AS 'Postponed',

总计（doc.doc\u total）为“doc”，总计（srv.srv\u check）为“srv”
key\u e.k:外键到employee.login kit.resp\u key:外键到key\u e doc.employee\u key/srv.employee\u key:外键到key\u e（两者相等，但kit.resp\u key不同）
我的问题是：

SELECT
    e.login,
    e.name,
    (SELECT SUM(k.detail_service LIKE '%Deal%') FROM kit k WHERE k.resp_key=key_e.key) AS 'Deal',
    (SELECT SUM(k.detail_service LIKE '%Not Deal%') FROM kit k WHERE k.resp_key=key_e.key) AS 'Not Deal',
    (SELECT SUM(k.detail_service LIKE '%Postponed%') FROM kit k WHERE k.resp_key=key_e.key) AS 'Postponed',
    (SELECT SUM(doc_total) FROM doc WHERE doc.employee_key=key_e.key) AS 'DOC',
    (SELECT SUM(srv_total) FROM srv WHERE srv.employee_key=key_e.key) AS 'SRV'
FROM
    employee e
INNER JOIN key_e ON
    e.login = key_e.login_employee
GROUP BY e.login, key_e.key

========================================
我所做的查询几乎返回了我想要的所有内容，除了行是重复的。由于kit的key与srv和doc的key不同，结果是重复的。我不知道怎么把srv和doc的钥匙和kit的钥匙连接起来。这个结构有点混乱，也许使用“join”可以解决这个问题，但我不知道如何解决。我使用“join”而不是这个内部select进行了一些查询，但到目前为止没有成功

我想你需要两个层次的聚合。尤其是，在执行联接之前，您希望在键级别进行聚合。然后要在登录级别进行聚合：

SELECT e.login, e.name,
       SUM(kit.num_Deal) as num_Deal,
       SUM(kit.num_not_deal) as num_not_deal,
       SUM(kit.num_Postponed) as num_Postponed,
       SUM(doc_total) as doc_total,
       SUM(srv_total) as srv_total
FROM employee e INNER JOIN
     key_e ON
     ON e.login = key_e.login_employee LEFT JOIN
     (SELECT doc.employee_key, SUM(doc.doc_total) as doc_total
      FROM doc
      GROUP BY doc.employee_key
     ) doc
     ON doc.employee_key = key_e.key LEFT JOIN
     (SELECT srv.employee_key, SUM(srv.srv_total) as srv_total
      FROM srv
      GROUP BY srv.employee_key
     ) srv
     ON srv.employee_key = key_e.key LEFT JOIN
     (SELECT kit.employee_key, 
             SUM(k.detail_service LIKE '%Deal%') as num_Deal,
             SUM(k.detail_service LIKE '%Not Deal%') as num_not_deal,
             SUM(k.detail_service LIKE '%Postponed%') as Postponed
      FROM kit k
      GROUP BY kit.employee_key
     ) kit
     ON kit.resp_key = key_e.key
GROUP BY e.login, e.name

如何将3个表连接成一个表，这些表的外键指向第四个表？

1条答案

相关问题

热门标签

最新问答