好的,我有两个相关的表,一个包含称为“ opportunities”的主字段,另一个包含名为“ opportunities_cstm”的附加字段。就我们而言,机会表包含以下字段:id和sales_stage。 careers_cstm表包含字段id_c和sales_stage_before_closed_c。 id_c是将两个表关联起来的内容。
sales_stage包含从1到10的值,还包含“ Closed Lost”(关闭丢失)或“ Closed Won”(关闭赢得)。在实际应用中,1到10代表从0-9%到90-99%的百分比范围,平仓亏损为0%,平仓赢率为100%。
sales_stage_before_closed_c是关闭前的百分比范围。
因此,在我的实际查询中,我需要为每个sales_stage显示一个百分比,其中有多少机会达到了这一阶段并带来了获胜的机会,有多少机会达到了这一阶段并导致了损失。
更新到更接近我需要的新查询:
SELECT opportunities_c_top.sales_stage_before_closed_c AS 'Sales Stage',
COUNT(*) * 100.0 /
( SELECT COUNT(*)
FROM `opportunities_cstm` opportunities_cstm join
`opportunities` opportunities
on opportunities_cstm.id_c = opportunities.id WHERE opportunities.`sales_stage` = 'Closed Won' AND opportunities_cstm.sales_stage_before_closed_c = opportunities_c_top.sales_stage_before_closed_c ) AS 'Closed Won',
COUNT(*) * 100.0 /
( SELECT COUNT(*)
FROM `opportunities_cstm` opportunities_cstm join
`opportunities` opportunities
on opportunities_cstm.id_c = opportunities.id WHERE opportunities.`sales_stage` = 'Closed Lost' AND opportunities_cstm.sales_stage_before_closed_c = opportunities_c_top.sales_stage_before_closed_c ) AS 'Closed Lost'
FROM `opportunities_cstm` opportunities_c_top join
`opportunities` opportunities_top
on opportunities_top.id = opportunities_c_top.id_c
WHERE (opportunities_top.`sales_stage` = 'Closed Won' OR opportunities_top.`sales_stage` = 'Closed Lost')
GROUP BY opportunities_c_top.sales_stage_before_closed_c
http://sqlfiddle.com/#!2/ac28d/1
它仍然不是100%正确,但是,如果您运行查询,它将显示60%-69%均为200,而不是每侧50。
最佳答案
SQL并非真正用于演示。我会考虑只提取原始数据,然后在PHP中进行操作。
SELECT
opportunities.`sales_stage`,
opportunities_cstm.`percent_before_closed_c`,
count(*)
FROM `opportunities` opportunities join
`opportunities_cstm` opportunities_cstm
on opportunities.id = opportunities_cstm.id_c
WHERE opportunities.`sales_stage` in ('Closed Lost', 'Closed Won')
GROUP BY opportunities.`sales_stage`, opportunities_cstm.`percent_before_closed_c`
除非我完全忽略了这一点。
关于php - 具有百分比明细的MySQL子查询(困难查询),我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/21380019/