mysql - SQL - 将多个值除以多个值

Question

Select AdClick.AdMonth AS DATE,
(SELECT SUM(AdBudget) FROM AdBudget GROUP BY AdMonth) / 
(SELECT SUM(AdClicks) FROM AdClick, AdReport 
WHERE AdReport.AdSourceID != 4
AND AdReport.AdSourceID = AdClick.AdSourceID
GROUP BY AdMonth) AS CPC
FROM AdClick, AdBudget, AdReport
WHERE AdClick.AdYear = '2016' 
GROUP BY AdClick.AdMonth

SUM(AdBudget) 和 SUM(AdClicks) 在表格中附加了 AdMonth 值来分隔月份，但我需要将具有相同月份值的 SUM 相除。分组不起作用，因为我不能将一个子查询中的多个值除以另一个具有多个值的子查询，所以有没有办法将 AdMonth 1 的总和除以 AdMonth 1 的总和并将 2 的总和除以2...?

仅供引用:我正在使用代码创建 ColdFusion 图表，但这些查询是在 ColdFusion 文档中调用的，因此与图表配合使用的 ColdFusion 解决方案并非不可能。

Answer 1

应该是这样的。根据月份和源 ID 将表内部连接在一起，并按月份分组:

SELECT   AdClick.AdMonth  AS `DATE`
         , SUM(AdBudget.AdBudget)
         , SUM(AdClick.AdClicks)
         , SUM(AdBudget.AdBudget) / SUM(AdClick.AdClicks)
FROM    AdClick
           INNER JOIN AdBudget ON AdClick.AdMonth = AdBudget.AdMonth
           INNER JOIN  AdReport ON AdReport.AdSourceID = AdClick.AdSourceID
WHERE   AdClick.AdYear = '2016' 
AND     AdReport.AdSourceID != 4
GROUP BY AdClick.AdMonth