在 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()
值的重复记录?
聚合函数在连接和分组发生后应用,并且仅适用于您正在聚合的列。因此,事件的一般顺序是从连接中复制数据,然后对其进行分组,然后聚合函数查看分组中的行,然后将其函数应用于这些行(仅适用于该列)。
因此,
MAX(companies_notes.'notes')
它会查看分组中每个客户的两个注释,并选择最后一个(按字典顺序排序)。因为COUNT(orders.id)
它类似地查看分组内的所有行(其中 136 行是因为连接到注释表),然后对它们进行计数。这就是为什么您最终仍将 136 作为numSales
. 每列的聚合函数是相互独立的。根据您的评论设置
companies_notes.company_id
唯一,肯定会解决问题(一旦您删除或合并了额外的条目以允许应用约束)。但与此同时,您需要做的是预处理,以确保在尝试加入之前
companies_notes
每个 仅有一个条目company_id
。有多种使用嵌套查询/派生表的技术。
我通常做的是应用行号,然后过滤到第一行。这需要将查询嵌套两层,但这意味着在外层您可以访问所选行中的所有字段。
您也可以使用 group-by
company_id
,然后在您希望在外部级别可用的字段上选择 MAX (或其他合适的聚合,如 GROUP_CONCAT)。需要明确的是,这两种解决方案都应用于嵌套查询内。然后加入这个嵌套查询。我主要用 TSQL 编写,所以如果以下语法不太正确,我深表歉意,但它显示了嵌套查询的总体思路: