mysql - 需要避免 join 中的乘法 sum()
问题描述
我的mysql查询是...
SELECT row_number() over (order by p.invoice_number) as 'S.No.'
, p.created_at as 'invoice_date'
, p.vendor_name
, p.invoice_number
, format(sum(p.cost),0,'en_IN') as total_cost
, v.accountno as accountno
, v.paid_date as paid_date
, format(sum(v.paid_amount),0,'en_IN') as paid_amount
from purchases p
join vendor_accounts v
on p.invoice_number = b.invoice_number
group
by p.invoice_number
having p.vendor_name = 'govardhan';
+-------+---------------------+-------------+----------------+------------+-----------+---------------------+-------------+
| S.No. | invoice_date | vendor_name | invoice_number | total_cost | accountno | paid_date | paid_amount |
+-------+---------------------+-------------+----------------+------------+-----------+---------------------+-------------+
| 1 | 2020-12-12 20:12:33 | govardhan | 061067 | 79,980 | JBL | 2020-12-12 20:38:30 | 2,50,000 |
+-------+---------------------+-------------+----------------+------------+-----------+---------------------+-------------+
输出乘以 vendor_name（10 次）和另一个表 vendor_name（2 次）总计 20
我的输出应该是
+-------+---------------------+-------------+----------------+------------+-----------+---------------------+-------------+
| S.No. | invoice_date | vendor_name | invoice_number | total_cost | accountno | paid_date | paid_amount |
+-------+---------------------+-------------+----------------+------------+-----------+---------------------+-------------+
| 1 | 2020-12-12 20:12:33 | govardhan | 061067 | 39,990 | JBL | 2020-12-12 20:38:30 | 2,50,00 |
+-------+---------------------+-------------+----------------+------------+-----------+---------------------+-------------+
请帮我解决问题。提前致谢。
样本数据：
mysql> select * from purchases where vendor_name='gowri';
+------+-------------+----------+-------+---------------+----------------+---------------+----------+-------------------+-------------+-------------------+----------------+---------------------+------------+
| id | vendor_name | location | cost | cost_of_price | invoice_number | serial_number | paid_GST | profit_percentage | sales_price | tag | barcode | created_at | updated_at |
+------+-------------+----------+-------+---------------+----------------+---------------+----------+-------------------+-------------+-------------------+----------------+---------------------+------------+
| 1654 | gowri | KPM | 2999 | 3149 | 3614 | 01 | 5 | 35 | 4251 | gowri/314/3614/01 | 16542012122028 | 2020-12-12 20:28:53 | NULL |
| 1655 | gowri | KPM | 2999 | 3149 | 3614 | 01 | 5 | 35 | 4251 | gowri/314/3614/01 | 16552012122028 | 2020-12-12 20:28:54 | NULL |
| 1656 | gowri | KPM | 2999 | 3149 | 3614 | 01 | 5 | 35 | 4251 | gowri/314/3614/01 | 16562012122028 | 2020-12-12 20:28:54 | NULL |
| 1657 | gowri | KPM | 2999 | 3149 | 3614 | 01 | 5 | 35 | 4251 | gowri/314/3614/01 | 16572012122028 | 2020-12-12 20:28:54 | NULL |
| 1658 | gowri | KPM | 2999 | 3149 | 3614 | 01 | 5 | 35 | 4251 | gowri/314/3614/01 | 16582012122028 | 2020-12-12 20:28:54 | NULL |
| 1659 | gowri | KPM | 2999 | 3149 | 3614 | 01 | 5 | 35 | 4251 | gowri/314/3614/01 | 16592012122028 | 2020-12-12 20:28:54 | NULL |
| 1660 | gowri | KPM | 2999 | 3149 | 3614 | 01 | 5 | 35 | 4251 | gowri/314/3614/01 | 16602012122028 | 2020-12-12 20:28:55 | NULL |
| 1661 | gowri | KPM | 2999 | 3149 | 3614 | 01 | 5 | 35 | 4251 | gowri/314/3614/01 | 16612012122028 | 2020-12-12 20:28:55 | NULL |
| 1662 | gowri | KPM | 2999 | 3149 | 3614 | 01 | 5 | 35 | 4251 | gowri/314/3614/01 | 16622012122028 | 2020-12-12 20:28:55 | NULL |
| 1663 | gowri | KPM | 2999 | 3149 | 3614 | 01 | 5 | 35 | 4251 | gowri/314/3614/01 | 16632012122028 | 2020-12-12 20:28:55 | NULL |
| 1664 | gowri | KPM | 3999 | 4199 | 3614 | 02 | 5 | 35 | 5669 | gowri/419/3614/02 | 16642012122030 | 2020-12-12 20:30:21 | NULL |
| 1665 | gowri | KPM | 3999 | 4199 | 3614 | 02 | 5 | 35 | 5669 | gowri/419/3614/02 | 16652012122030 | 2020-12-12 20:30:21 | NULL |
| 1666 | gowri | KPM | 3999 | 4199 | 3614 | 02 | 5 | 35 | 5669 | gowri/419/3614/02 | 16662012122030 | 2020-12-12 20:30:22 | NULL |
| 1667 | gowri | KPM | 3999 | 4199 | 3614 | 02 | 5 | 35 | 5669 | gowri/419/3614/02 | 16672012122030 | 2020-12-12 20:30:22 | NULL |
| 1668 | gowri | KPM | 3999 | 4199 | 3614 | 02 | 5 | 35 | 5669 | gowri/419/3614/02 | 16682012122030 | 2020-12-12 20:30:22 | NULL |
| 1669 | gowri | KPM | 3999 | 4199 | 3614 | 02 | 5 | 35 | 5669 | gowri/419/3614/02 | 16692012122030 | 2020-12-12 20:30:22 | NULL |
| 1670 | gowri | KPM | 3999 | 4199 | 3614 | 02 | 5 | 35 | 5669 | gowri/419/3614/02 | 16702012122030 | 2020-12-12 20:30:23 | NULL |
| 1671 | gowri | KPM | 3999 | 4199 | 3614 | 02 | 5 | 35 | 5669 | gowri/419/3614/02 | 16712012122030 | 2020-12-12 20:30:23 | NULL |
| 1672 | gowri | KPM | 3999 | 4199 | 3614 | 02 | 5 | 35 | 5669 | gowri/419/3614/02 | 16722012122030 | 2020-12-12 20:30:24 | NULL |
| 1673 | gowri | KPM | 3999 | 4199 | 3614 | 02 | 5 | 35 | 5669 | gowri/419/3614/02 | 16732012122030 | 2020-12-12 20:30:24 | NULL |
| 1694 | gowri | JBL | 46666 | 48999 | 3615 | 01 | 5 | 35 | 66149 | gowri/489/3615/01 | 16942012130215 | 2020-12-13 02:15:55 | NULL |
| 1695 | gowri | JBL | 46666 | 48999 | 3615 | 01 | 5 | 35 | 66149 | gowri/489/3615/01 | 16952012130215 | 2020-12-13 02:15:55 | NULL |
| 1696 | gowri | JBL | 46666 | 48999 | 3615 | 01 | 5 | 35 | 66149 | gowri/489/3615/01 | 16962012130215 | 2020-12-13 02:15:55 | NULL |
| 1697 | gowri | JBL | 46666 | 48999 | 3615 | 01 | 5 | 35 | 66149 | gowri/489/3615/01 | 16972012130215 | 2020-12-13 02:15:56 | NULL |
| 1698 | gowri | JBL | 46666 | 48999 | 3615 | 01 | 5 | 35 | 66149 | gowri/489/3615/01 | 16982012130215 | 2020-12-13 02:15:56 | NULL |
| 1699 | gowri | JBL | 46666 | 48999 | 3615 | 01 | 5 | 35 | 66149 | gowri/489/3615/01 | 16992012130215 | 2020-12-13 02:15:56 | NULL |
| 1700 | gowri | JBL | 46666 | 48999 | 3615 | 01 | 5 | 35 | 66149 | gowri/489/3615/01 | 17002012130215 | 2020-12-13 02:15:56 | NULL |
| 1701 | gowri | JBL | 46666 | 48999 | 3615 | 01 | 5 | 35 | 66149 | gowri/489/3615/01 | 17012012130215 | 2020-12-13 02:15:56 | NULL |
| 1702 | gowri | JBL | 46666 | 48999 | 3615 | 01 | 5 | 35 | 66149 | gowri/489/3615/01 | 17022012130215 | 2020-12-13 02:15:56 | NULL |
| 1703 | gowri | JBL | 46666 | 48999 | 3615 | 01 | 5 | 35 | 66149 | gowri/489/3615/01 | 17032012130215 | 2020-12-13 02:15:56 | NULL |
| 1704 | gowri | JBL | 46666 | 48999 | 3615 | 01 | 5 | 35 | 66149 | gowri/489/3615/01 | 17042012130215 | 2020-12-13 02:15:56 | NULL |
| 1705 | gowri | JBL | 46666 | 48999 | 3615 | 01 | 5 | 35 | 66149 | gowri/489/3615/01 | 17052012130215 | 2020-12-13 02:15:56 | NULL |
| 1706 | gowri | JBL | 46666 | 48999 | 3615 | 01 | 5 | 35 | 66149 | gowri/489/3615/01 | 17062012130215 | 2020-12-13 02:15:56 | NULL |
| 1707 | gowri | JBL | 46666 | 48999 | 3615 | 01 | 5 | 35 | 66149 | gowri/489/3615/01 | 17072012130215 | 2020-12-13 02:15:56 | NULL |
| 1708 | gowri | JBL | 46666 | 48999 | 3615 | 01 | 5 | 35 | 66149 | gowri/489/3615/01 | 17082012130215 | 2020-12-13 02:15:56 | NULL |
+------+-------------+----------+-------+---------------+----------------+---------------+----------+-------------------+-------------+-------------------+----------------+---------------------+------------+
35 rows in set (0.00 sec)
mysql> select * from vendor_accounts where vendor_name='gowri';
+----+-------------+----------------+-----------+-------------+---------------------+
| id | vendor_name | invoice_number | accountno | paid_amount | paid_date |
+----+-------------+----------------+-----------+-------------+---------------------+
| 2 | gowri | 3614 | NULL | NULL | 2020-12-12 20:28:55 |
| 3 | gowri | 3614 | NULL | NULL | 2020-12-12 20:30:25 |
| 10 | gowri | 3614 | XXX1035 | 25000 | 2020-12-13 02:13:52 |
| 11 | gowri | 3615 | NULL | NULL | 2020-12-13 02:15:57 |
+----+-------------+----------------+-----------+-------------+---------------------+
4 rows in set (0.00 sec)
实际输出应该看起来像
+-------+---------------------+-------------+----------------+------------+---------------------+-------------+
| S.No. | invoice_date | vendor_name | invoice_number | total_cost | paid_date | paid_amount |
+-------+---------------------+-------------+----------------+------------+---------------------+-------------+
| 1 | 2020-12-12 20:28:53 | gowri | 3614 | 69,980 | 2020-12-12 20:28:55 | 5,00,000 |
| 2 | 2020-12-13 02:15:55 | gowri | 3615 | 6,99,990 | 2020-12-13 02:15:57 | NULL |
| | | | TOTAL | 7,69,970 | | 25,000 |
+-------+---------------------+-------------+----------------+------------+---------------------+-------------+
解决方案
对于给定的供应商/发票元组，两个表中都有几行，因此连接乘以行，并且您在聚合函数中得到错误的结果。
您需要先在子查询中预先聚合，然后加入：
select
row_number() over (order by p.invoice_number) as rn,
p.*,
va.total_paid_amount,
va.max_paid_date
from (
select vendor_name, invoice_number,
sum(p.cost) as total_cost,
max(created_at) as max_created_at
from purchases
group by vendor_name, invoice_number
) p
inner join (
select vendor_name, invoice_number,
sum(paid_amount) as total_paid_amount,
max(paid_date) as max_paid_date
from vendor_accounts
group by vendor_name, invoice_number
) va on va.invoice_number = p.invoice_number and va.vendor_name = p.vendor_name
请注意，我在日期列上使用了聚合函数；与您的原始查询不同。这些列不是group by子句的一部分，因此我们需要对它们进行聚合。
这会立即为所有供应商带来结果。where您可以通过在查询末尾添加子句轻松更改查询以过滤给定的供应商名称。
推荐阅读
- android - Observable 在 rxjava 和 rxandroid 中的正确解释
- php - 使用 PHP Whois 库获取到期日期？
- android - 模块的元数据版本不匹配...在 ionic3 中找到预期版本 3
- java - Java 什么时候读取方法字节码？
- java - junit中的TestRestTemplate
- rxjs - RxJS 6 静态合并在哪里？
- asp.net-mvc - 自定义编辑器用于模板和 htmlAttributes
- markdown - 带有 .md 的 Jekyll + Github 页面返回 404
- api - JwtSecurityToken 过期日期相隔两个小时
- java - 如何在通话仍处于活动状态时隐藏拨号器意图