在 MySQL 中,我有表,简化如下:
companies (id, name, rank, trade_sector_id)
orders (id, company_id, paid, subtotal)
trade_sectors (id, label)
companies_notes (id, company_id, notes)
我想要每个公司占一行,其中包含:
- 公司名称
- 订单数量
- 所有小计总计
- 公司笔记
为了简化,我在这里只选择一家公司,其名称为id=14401
. 它有 68 个订单:
SELECT
companies.id AS company_id,
companies.account_name,
COUNT(orders.id) AS numSales,
SUM(orders.`subtotal`) AS subtotal,
MAX(trade_sectors.label) AS trade_sector,
MAX(companies_notes.`notes`) AS notes
FROM companies
LEFT JOIN `orders` ON (companies.id = orders.`company_id` AND orders.`paid` = 1)
LEFT JOIN `trade_sectors` ON (companies.trade_sector_id = trade_sectors.`id`)
LEFT JOIN `companies_notes` ON (`companies_notes`.`company_id` = companies.id)
WHERE companies.id = '14401'
GROUP BY companies.id
ORDER BY companies.rank DESC;
问题
该公司有 68 个订单,但我得到的 numSales 为 136(因此是数字的 2 倍),而且小计也比应有的大 2 倍。
但如果我删除注释的连接,它是正确的:
SELECT
companies.id AS company_id,
companies.account_name,
COUNT(orders.id) AS numSales,
SUM(orders.`subtotal`) AS subtotal,
MAX(trade_sectors.label) AS trade_sector
FROM companies
LEFT JOIN `orders` ON (companies.id = orders.`company_id` AND orders.`paid` = 1)
LEFT JOIN `trade_sectors` ON (companies.trade_sector_id = trade_sectors.`id`)
WHERE companies.id = '14401'
GROUP BY companies.id
ORDER BY companies.rank DESC;
因此,注释连接似乎为每个订单提供了 2 行。是的,该公司有两个注释行(应该只有 1 个),但这在技术上并没有强制执行。我认为通过在companies_notes
. notes
只会考虑其中之一。事实上,Group BY 子句要求对列进行聚合。
如何防止连接创建影响SUM()
和MAX()
值的重复记录?