mysql - SQL 查询 : How to get data from four different tables

Question

我正在使用 mysql，这是我拥有的模式。

第一个表:关键字

+------------+-------------+
| keyword_id | keyword_tag |
+------------+-------------+
|          2 | marketing   |
|         58 | sales       |
|         59 | scraping    |
|          1 | seo         |
|          3 | testkeyword |
+------------+-------------+

第二个表:域

+-----------+-----------------+---------------+---------------------+-----------------+
| domain_id | domain_name     | campaign_name | campaign_date       | campaign_note   |
+-----------+-----------------+---------------+---------------------+-----------------+
|         1 | test.org        | campaign 1    | 2019-08-27 17:10:58 | Test            |
|        11 | example.org     | campaign 2    | 2019-08-27 17:36:06 | Campaign Note 2 |
+-----------+-----------------+---------------+---------------------+-----------------+

第三个表:Domain_Keywords

+-------+-----------+------------+
| dk_id | domain_id | keyword_id |
+-------+-----------+------------+
|     1 |         1 |          1 |
|     2 |         1 |          2 |
|     3 |         1 |          3 |
|     4 |        11 |          1 |
|     5 |        11 |          2 |
|     6 |        11 |         58 |
|     7 |        11 |         59 |
+-------+-----------+------------+

第四张表:电子邮件

+----------+-----------------------+-------+--------------+-------+----------------+-----------+---------+
| email_id | email                 | valid | is_generated | score | number_results | domain_id | user_id |
+----------+-----------------------+-------+--------------+-------+----------------+-----------+---------+
|        1 | b1@test.org           |     1 |            1 |   0.5 |              2 |        1  |       3 |
|        2 | b2@test.org           |     1 |            1 |   0.3 |              0 |        1  |       1 |
|        3 | a1@example.org        |     1 |            1 |   0.3 |              0 |        11 |       4 |
|        4 | a2@example.org        |     1 |            1 |   0.3 |              0 |        11 |       4 |
|        5 | a3@example.org        |     1 |            1 |   0.3 |              0 |        11 |       1 |
|        6 | a4@example.org        |     1 |            1 |   0.5 |              3 |        11 |       3 |
+----------+-----------------------+-------+--------------+-------+----------------+-----------+---------+

我想显示如下数据:

请指导我如何从这四个表中查询数据。谢谢

Answer 1

因为给定的 domain_id 在每个表中可以有多个行，所以有必要在派生表中执行所有聚合，然后将它们连接到 Domains 表。

SELECT d.campaign_name,
       d.campaign_date,
       COALESCE(e.num_emails, 0) AS num_emails,
       COALESCE(e.num_generated_emails, 0) AS num_generated_emails,
       k.keywords
FROM Domains d
JOIN (SELECT dk.domain_id, 
             GROUP_CONCAT(k.keyword_tag) AS keywords
      FROM Domain_Keywords dk
      JOIN Keywords k ON k.keyword_id = dk.keyword_id
      GROUP BY dk.domain_id) k ON k.domain_id = d.domain_id
LEFT JOIN (SELECT domain_id,
                  COUNT(*) AS num_emails,
                  SUM(is_generated) AS num_generated_emails
           FROM Emails
           GROUP BY domain_id) e ON e.domain_id = d.domain_id

输出:

campaign_name   campaign_date       num_emails  num_generated_emails    keywords
campaign 1      2019-08-27 17:10:58 2           2                       seo,marketing,testkeyword
campaign 2      2019-08-27 17:36:06 4           4                       seo,marketing,sales,scraping

Demo on dbfiddle