mysql内连接导致乘法

发布于 2024-11-18 05:32:38 字数 863 浏览 1 评论 0原文

基本上我有这样的结构：

交易有并属于许多渠道
交易有很多 DealSales
交易属于渠道

当我想查找交易销售的金额时，我使用这个查询：

SELECT targets.id,SUM(deal_sales.amount_sold) AS amount_sold
FROM deal_sales 
INNER JOIN deals ON deals.id = deal_sales.deal_id 
INNER JOIN targets ON deals.target_id = targets.id 
WHERE targets.approved = 1 AND targets.active = 1
GROUP BY targets.id

它工作得很好，问题是当我需要时按渠道过滤，查找渠道中交易的销售金额：

SELECT targets.id,SUM(deal_sales.amount_sold) AS amount_sold
FROM deal_sales 
INNER JOIN deals ON deals.id = deal_sales.deal_id 
INNER JOIN targets ON deals.target_id = targets.id 
**INNER JOIN channels_deals ON channels_deals.deal_id = deals.id**
WHERE targets.approved = 1 AND targets.active = 1
GROUP BY targets.id

当我将联接添加到渠道表时，amount_sold 会乘以与交易相关的每个渠道。我怎样才能避免这种情况？

原文

basically i have this structure:

Deal has and belongs to many Channels
Deal has many DealSales
Deal belongs to Channel

When i want to find the amount sold by a deal, i use this query:

SELECT targets.id,SUM(deal_sales.amount_sold) AS amount_sold
FROM deal_sales 
INNER JOIN deals ON deals.id = deal_sales.deal_id 
INNER JOIN targets ON deals.target_id = targets.id 
WHERE targets.approved = 1 AND targets.active = 1
GROUP BY targets.id

its working just fine, the problem is when i need to filter by channel, find the amount sold by a deal in a channel:

SELECT targets.id,SUM(deal_sales.amount_sold) AS amount_sold
FROM deal_sales 
INNER JOIN deals ON deals.id = deal_sales.deal_id 
INNER JOIN targets ON deals.target_id = targets.id 
**INNER JOIN channels_deals ON channels_deals.deal_id = deals.id**
WHERE targets.approved = 1 AND targets.active = 1
GROUP BY targets.id

When i add the join to channels table, the amount_sold is multiplied by each channel a deal has relation with. How can i avoid this?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

甜柠檬 2024-11-25 05:32:38

使用 IN 或 Exists

例如

SELECT targets.id,SUM(deal_sales.amount_sold) AS amount_sold
FROM deal_sales 
INNER JOIN deals ON deals.id = deal_sales.deal_id 
INNER JOIN targets ON deals.target_id = targets.id 

WHERE targets.approved = 1 AND targets.active = 1
  and 
   deals.id IN (SELECT deal_id from channels_deals where something = 1)
GROUP BY targets.id

Use IN or Exists

for example

SELECT targets.id,SUM(deal_sales.amount_sold) AS amount_sold
FROM deal_sales 
INNER JOIN deals ON deals.id = deal_sales.deal_id 
INNER JOIN targets ON deals.target_id = targets.id 

WHERE targets.approved = 1 AND targets.active = 1
  and 
   deals.id IN (SELECT deal_id from channels_deals where something = 1)
GROUP BY targets.id

回复收藏 0 原文

假装爱人 2024-11-25 05:32:38

Try this:

SELECT channels_deals.deal_id,SUM(deal_sales.amount_sold) AS amount_sold
FROM deal_sales 
INNER JOIN deals ON deals.id = deal_sales.deal_id 
INNER JOIN targets ON deals.target_id = targets.id 
INNER JOIN channels_deals ON channels_deals.deal_id = deals.id
WHERE targets.approved = 1 AND targets.active = 1
GROUP BY channels_deals.deal_id

Try this:

SELECT channels_deals.deal_id,SUM(deal_sales.amount_sold) AS amount_sold
FROM deal_sales 
INNER JOIN deals ON deals.id = deal_sales.deal_id 
INNER JOIN targets ON deals.target_id = targets.id 
INNER JOIN channels_deals ON channels_deals.deal_id = deals.id
WHERE targets.approved = 1 AND targets.active = 1
GROUP BY channels_deals.deal_id

回复收藏 0 原文

~没有更多了~