mysql - SQL 查询:如何从四个不同的表中获取数据
问题描述
我正在使用 mysql,这是我拥有的架构。
第一张表:Keywords
+------------+-------------+
| keyword_id | keyword_tag |
+------------+-------------+
| 2 | marketing |
| 58 | sales |
| 59 | scraping |
| 1 | seo |
| 3 | testkeyword |
+------------+-------------+
第二张表:Domains
+-----------+-----------------+---------------+---------------------+-----------------+
| 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 |
+-------+-----------+------------+
第四张表:Emails
+----------+-----------------------+-------+--------------+-------+----------------+-----------+---------+
| 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 |
+----------+-----------------------+-------+--------------+-------+----------------+-----------+---------+
请指导我如何从这四个表中查询数据。谢谢
解决方案
因为对于给定的每个表可以有多个行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
推荐阅读
- javascript - 使用 fetch API 查询端点以获取 json 响应并返回空响应错误
- java - 例外处理
- java - 空指针异常,因为尚未创建活动
- jenkins - Docker 容器内的 Jenkins Pipeline 使用 SSH 密钥进行 SSH 文件传输
- java - 问题打开搜索视图只有零位
- python - 如何以高精度打印 E 数?使用我自己的价值
- kubernetes - 彻底移除 Kubernetes
- amazon-web-services - Fargate 和 SSM 的 AWS 权限
- cmake - 如何在 ROS 包中使用/链接介子构建 Gstreamer?
- sql-server - 如何使用 SQL 将百分比添加到我的表中?