mysql - 合并两个表唯一列
问题描述
我有两个表,如下所示:
mysql> select*from receipt;
+----+------------+-------+---------------------+
| id | receipt_id | money | created_at |
+----+------------+-------+---------------------+
| 1 | receipt_3 | 100 | 2018-10-30 00:00:00 |
| 2 | receipt_4 | 200 | 2018-10-29 00:00:00 |
| 3 | receipt_5 | 300 | 2018-10-31 00:00:00 |
+----+------------+-------+---------------------+
3 rows in set (0.00 sec)
mysql> select*from material;
+----+------------+---------------------+
| id | receipt_id | created_at |
+----+------------+---------------------+
| 1 | receipt_3 | 2018-10-30 00:00:00 |
| 2 | receipt_3 | 2018-10-30 00:00:00 |
| 3 | receipt_5 | 2018-10-31 00:00:00 |
+----+------------+---------------------+
3 rows in set (0.00 sec)
我得到了使用这个的结果:“select distinct sum(money), material
. created_at
from receipt
inner join material
on receipt
. receipt_id
= material
. receipt_id
group by material
. created_at
;”
+------------+---------------------+
| sum(money) | created_at |
+------------+---------------------+
| 200 | 2018-10-30 00:00:00 |
| 300 | 2018-10-31 00:00:00 |
+------------+---------------------+
2 rows in set (0.00 sec)
但我想通过忽略相同的记录来得到这个:
+------------+---------------------+
| sum(money) | created_at |
+------------+---------------------+
| 100 | 2018-10-30 00:00:00 |
| 300 | 2018-10-31 00:00:00 |
+------------+---------------------+
2 rows in set (0.00 sec)
想了很久还是没有答案,请大家帮忙,非常感谢。
解决方案
只需使用sum(distinct money)
:
select sum(distinct money) as sum_money,
m.created_at
from receipt r join material m
on r.receipt_id=m.receipt_id
group by m.created_at;
+------------+---------------------+
| sum_money | created_at |
+------------+---------------------+
| 100 | 2018-10-30 00:00:00 |
| 300 | 2018-10-31 00:00:00 |
+------------+---------------------+
推荐阅读
- android - 使用登录页面
- react-native - 无法在 Jenkins 上构建 APK 文件(反应原生)
- react-native - 更新由以下人员管理的视图的属性“d”时出错:RNSVGPath
- c# - 使用 Chrome Headless 进行 Selenium C# 测试 - Chrome 文件上传窗口不起作用
- ionic-framework - 如何使用 Angular js 在 ionic1 中包含 npm 模块
- php - 如何在laravel中不包含html的情况下发送邮件?
- django - 带有隐藏密码字段 Django 的手动密码
- python - 无法从异步函数内部产生并获取产生的数据
- python - 通过属性在列表中查找对象
- python - 通过 COM API python 从 Jenkins 启动 Enterprise Architect 时访问被拒绝